Sql-server – Error in running SQL Server script

scriptingsql serversql-server-2008-r2

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:

Clean the data of newDatabase
Generate data only script of oldDatabase
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?

I run my script using this command ,

sqlcmd -S myServerName -U userName -P passowrd -i PathOfMyScriptFile

Best Answer

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.

Related Solutions

Sql-server – IDENTITY_INSERT seems stuck

Is it possible you have two tables with the same name in different schemas? Some random doc I found online states that message 8107 appears when you already have IDENTITY_INSERT set to ON for another table in the db...

Another debugging step to check: You state that the issue keep occurring even after you drop and recreate the table, but what if you drop and don't recreate the table, and try to enable IDENTITY_INSERT on another table? What happens then?

Sql-server – SQL Server 2008 R2 Transactional Replication “Cannot insert explicit value for identity column…”

Just to add that you don't have to drop and re-create the replication just to change the "not for replication" bit.

You can do it using T-SQL without generating a snapshot or breaking your replication --

sys.sp_identitycolumnforreplication

1 = not for replication

0 = for replication and this causes the problems with Identity colums on subscriber side

To change it for all the tables :

EXEC sp_msforeachtable @command1 = '
declare @int int
set @int =object_id("?")
EXEC sys.sp_identitycolumnforreplication @int, 1'

To change it for just 1 table, first find out the object_id of the table and then run below

EXEC sys.sp_identitycolumnforreplication table_object_id, 1

EDIT

Below tsql will give you a nice output with command that can be reviewed before running for entire database :

if exists (select 1 from sys.identity_columns where is_not_for_replication = 0)
begin
SELECT  QUOTENAME(SCHEMA_NAME(t.schema_id)) as SchemaName,  
        QUOTENAME(t.name) AS TableName, 
        c.name AS ColumnName,
        c.object_id as ObjectID,
        c.is_not_for_replication,
        'EXEC sys.sp_identitycolumnforreplication '+cast(c.object_id as varchar(20)) + ', 1 ;' as CommandTORun_SetIdendityNOTForReplication
    FROM    sys.identity_columns AS c 
        INNER JOIN  sys.tables AS t ON t.[object_id] = c.[object_id]
        WHERE   c.is_identity = 1
        and c.is_not_for_replication = 0
end
else 
print 'There are no identity columns that needs NOT FOR REPLICATION set to 1'

For adventureworks database :

Best Answer

Related Solutions

Sql-server – IDENTITY_INSERT seems stuck

Sql-server – SQL Server 2008 R2 Transactional Replication “Cannot insert explicit value for identity column…”

EDIT

Related Question