Need column names in Insert statements
prabhubravo
Posts: 7
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,
, :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
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,
, :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
Comments
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.
Project Manager
Red Gate Software Ltd
Regards,
Prabhu
Project Manager
Red Gate Software Ltd
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.