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)
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
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