What are the challenges you face when working across database platforms? Take the survey

Need column names in Insert statements

prabhubravoprabhubravo Posts: 7
edited July 30, 2014 2:17AM in Data Compare for Oracle
I use Data compare for oracle. The tool works fine comparing the tables but when I generate the sqls for the differences, the insert statements doesnot have an explicit column name in the insert statements. This causes errors when I run the scripts into another database, as the column order for that table in that new database might not be same as the target database to which I compared to.
Is there an option to include the column names in the scripts?
process followed:
Compare Db1 and Db2, generate sqls for the difference that is then run on Db3. (We cannot access Db3 to do the comparison)

Script generated by Red Gate:

statement1 := 'INSERT INTO position VALUES (:0, :1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11)';
EXECUTE IMMEDIATE statement1 USING value0, value1, value2, value3, value4, value5, value6, value7, value8, value9, value10, value11;

When I run this sql in a different database, the same table 'position' has a different ordering of columns.



  • Options
    Nice idea, and having a quick look at it this morning (pre-coffee) I've implemented the option.

    This should make it into the next release which I hope will be in the next few weeks. If you need it more urgently you can contact support@red-gate.com to get the latest build.

    What a great way to start the morning, that's really cheered me up after all my LDAP woes yesterday, thanks.
    Richard Mitchell
    Project Manager
    Red Gate Software Ltd
  • Options
    Excellent !! that was quick turn around. I have emailed the support team to get me the latest build.

  • Options
    Happy to help :)
    Richard Mitchell
    Project Manager
    Red Gate Software Ltd
  • Options
    VasuVasu Posts: 1 New member
    Still we are seeing same issue, some of the tables able to generate column names and some of the tables generating bind values.

    Example 1:INSERT INTO tablename (value1,value2,value3) VALUES (:0, :1, :2) --This format is working fine.
    Example 2: INSERT INTO tablename VALUES (:0, :1, :2)'; --This format is having issue.

    My Redgare version is
Sign In or Register to comment.