I am currently in the process of migrating data from one server / database to another, and here's a tried and tested method that I have used for certain tables: NEGATIVE IDENTITIES

NOTE:I do all transformations in a 'StageDB' database, which is a restored copy of the Source database (both Schema and Data)- so I'm not doing any updates in the source database at all OR any inserts directly into the destination database until the data is in its final state)

Here are the steps that you need to take:

1) Drop all Foreign Key Constraints that relate to StageDB.dbo.Customers table

2) Delete all records from the StageDB.dbo.Customers table

3) Make the following changes to StageDB.dbo.Customers table:

- Add additional Integer field - 'PreviousCustomerID'

- set the CustomerID Primary Key Identity field to Negative Integer as follows:

CustomerID INT NOT NULL IDENTITY (-1, -1)

4) Insert data from SourceDB.dbo.Customers to StageDB.dbo.StageCustomers as follows:

INSERT INTO StageDB.dbo.Customers

(

PreviousCustomerID

--add all other Customer table fields here except the CustomerID

)

SELECT

CustomerID

--select all other Customer table fields

FROM

SourceDB.dbo.Customers;

4) The StageDBdbo.Customers table is now populated with Negative Identity Integers on the Primary Key field CustomerID, with the additional field 'PreviousCustomerID' which will preserve the original CustomerID Value (as suggested in previous post)

We can use this to update any tables related to the Customers table

5) Update any tables related to the Customers table in the StageDB database as follows -

UPDATE RelatedCustomerTable

SET CustomerID=cust.CustomerID

FROM StageDB.dbo.Customers as cust

WHERE cust.CustomerID=RelatedCustomerTable.PreviousCustomerID

6) Repeat Step 5 for any table that has the CustomerID field, ensuring that any Foreign Key Constraints are dropped before updating

7) Once all updates are complete - Add any Foreign Key Constraints that were originally dropped in the StageDB database. This will act as a data quality check to ensure that all IDs have been updated to match the new PKs

8) Add the new 'PreviousCustomerID' field on the DestinationDB.dbo.Customers table

9) For the final insert from the StageDB database into the DestinationDB database, you must drop the Identity from the Primary Key on the DestinationDB.dbo.Customers table so that the negative Identities are not overwritten

10) Insert data directly from the StageDB.dbo.Customers into DestinationDB.dbo.Customers

11) Add the Identity back onto the Primary Key of Customers Table

12) Insert the data from all other tables from StageDB database

*****The process is now complete*****

Using this method, you can ensure that there will never be any clash in identities and it is easy to identify which data is from migration and which data is not

TIP 1: If this data migration is not a one off procedure, it is best to wrap the stages in a transaction AND/OR put the steps in an SSIS Package - either way, ensure that you implement effective error handling and rollback routines

TIP 2: Again - if this data migration is not a one off procedure, by using the same StageDB.dbo.Customers table each time, by using the Delete statement, rather than Truncate - your maximum identity value for CustomerID will be retained so you won't be duplicating negative identities in the DestinationDB

I hope this helps,

Jo