How can we help you today? How can we help you today?

Need column names in Insert statements

Hi,
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.


Thanks
prabhubravo
0

Comments

4 comments

Sort by
  • richardjm
    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.
    richardjm
    0
  • prabhubravo
    Excellent !! that was quick turn around. I have emailed the support team to get me the latest build.

    Regards,
    Prabhu
    prabhubravo
    0
  • richardjm
    Happy to help :)
    richardjm
    0
  • Vasu
    Hi, 
    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 3.1.16.2198.
    Vasu
    0

Add comment

Please sign in to leave a comment.