Can't generate insert scripts without Primary Key
jonswaino
Posts: 18
Hi,
I'm trying to generate insert scripts for a database. I need to run the script across the live servers. The way I have done it is to create an empty table on a test database, then compare the source and destination tables. This generates successfully about 500 insert statements.
However, the Primary Key is being added into the scripts and I don't want it to. The reason is the data was originally imported from Excel and the primary key is sometimes duplicated so it won't work. I don't mind SQL automatically using the Identity option when each row is inserted.
If I uncheck the 'Include Identity Columns' in SQLDataCompare this has no effect. I'm guessing because I'm comparing against an empty table it is included the primary key anyway.
Can you provide any assistance, this is becoming a bit of a headache!
I'm trying to generate insert scripts for a database. I need to run the script across the live servers. The way I have done it is to create an empty table on a test database, then compare the source and destination tables. This generates successfully about 500 insert statements.
However, the Primary Key is being added into the scripts and I don't want it to. The reason is the data was originally imported from Excel and the primary key is sometimes duplicated so it won't work. I don't mind SQL automatically using the Identity option when each row is inserted.
If I uncheck the 'Include Identity Columns' in SQLDataCompare this has no effect. I'm guessing because I'm comparing against an empty table it is included the primary key anyway.
Can you provide any assistance, this is becoming a bit of a headache!
Comments
You can exclude the PK column from the synchronization, but only if you don't use it as the comparison key in the project. By default, SQL Data Compare will use the primary key as the comparison key, but you can set up your own custom key, or custom composite key to use instead. Once you do this, you can exclude the PK column.
You can set this on the 'Tables & Views' tab of project configuration.
I hope this helps.
I've followed what you said, and instead use the 'DESCRIPTION' column of my table as the key from which to compare. I've also set the option on the project to 'Ignore Primart Keys'. When I perform the comparison, and then generate the INSERT scripts, I still get the INSERT statement including the primary key column AND the primary key value.
I don't want SQL Data Compare to do anything with the primary key. Is there anyway I am doing this in the wrong way or an alternative solution to this problem. I'm effectively wanting to remove a column from the comparison along with generating any data for that column.
** UPDATE **
I think I've resolved the issue, I just needed to unmap the columns that I wasn't interested in so everything is ok now. Thanks for your help.
You can also exclude the column by unchecking the PK column from 'Columns In Comparison' on the 'Tables & Views' tab