1

I copied oldDatabase as newDatabase. Modified newDatabase's schema.
Meanwhile, oldDatabase's data are processing and updating.

I want to copy oldDatabase's data to newDatabase.

What I have done is:

  1. Clean the data of newDatabase
  2. Generate data only script of oldDatabase
  3. Run this script in newDatabase.

And I got a lot of error messages like:

Cannot insert explicit value for identity column in table 'MyTableName' when   
IDENTITY_INSERT is set to OFF.

Invalid column name 'MyColumnName'.

Violation of PRIMARY KEY constraint 'PK_MyTableName'. Cannot insert duplicate  
key in object 'dbo.MyTableName'.

Some tables and rows data are affected. But, tables with above error are not affected. How can I fix it? Is there a best way to make this stuff?

PS

I run my script using this command ,

sqlcmd -S myServerName -U userName -P passowrd -i PathOfMyScriptFile
  • What schema changes did you make to newDatabase? Scripting the data from oldDatabase will have no idea about these changes. – Jon Seigel Aug 17 '13 at 14:11
1

You have three different types of problems from the looks of it.


Cannot insert explicit value for identity column in table 'MyTableName' when   
IDENTITY_INSERT is set to OFF.

You have identity columns. For a table that has an identity column you have three choices. First when you do your insert exclude the identity column. Second remove the identity property from the column in the destination table. Third use identity_insert. To use identity_insert you first turn it on for the table.

SET IDENTITY_INSERT MyTableName ON

Then you do your insert. Note: you have to list all of the columns in the insert.

INSERT INTO MyTableName (fieldlist) VALUES (valuelist)

Not

INSERT INTO MyTableName VALUES (valuelist)

And last but not least make sure you turn identity_insert back off again. Identity_insert can only be turned on for one table at a time for a given connection.

SET IDENTITY_INSERT MyTableName OFF

Here is a good walkthrough for insert into identity columns.


Invalid column name 'MyColumnName'.

You said you changed the structure of 'newDatabase'. Well my guess is you removed 'MyColumnName'. Either exclude this from the insert or add the column back. You either need to make sure the structure in newDatabase matches oldDatabase or account for the differences in your inserts.


Violation of PRIMARY KEY constraint 'PK_MyTableName'. Cannot insert duplicate  
key in object 'dbo.MyTableName'.

Last but not least you are inserting data into newDatabase were the data already exists. Or at least the primary key values already exist in newDatabase.MyTableName. My suggestion here is if there is any chance that you are doing updates as well as inserts change your INSERT statements to MERGE statements. Otherwise add a WHERE NOT EXISTS or something similar to the SELECT statement in your INSERT statement to exclude any rows where the primary key already exists in newDatabase.MyTableName.

2

What your script is doing is explicitly attempting to insert data into an IDENTITY column. You need to do one of two things: Either set IDENTITY_INSERT to ON for that table prior to the insert, or rework the INSERT statement so that it doesn't have the IDENTITY column specified with a value.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.