I want to migrate data from one database to another. The table schemas are exactly the same:
CREATE TABLE Customers( [Id] INT NOT NULL PRIMARY KEY IDENTITY, (some other columns ......) ); CREATE TABLE Orders( [Id] INT NOT NULL PRIMARY KEY IDENTITY, [CustomerId] INT NOT NULL, (some other columns ......), CONSTRAINT [FK_Customers_Orders] FOREIGN KEY ([CustomerId]) REFERENCES [Customers]([Id]) )
The two databases have different data, so the new identity key for the same table would be different at the two databases. That is not a problem; my goal is to append new data to the existing one, not complete replacing all data of the entire table. However I would like to maintain all parent-child relationship of the inserted data.
If I use the "Generate Script" feature of SSMS, the script would attempt to insert using the same ID, which would conflict existing data in the destination database. How can I copy data using database scripts only?
I want the identity column at the destination to continue normally from its last value.
Customers does not have any other
UNIQUE NOT NULL constraint. It is ok to have duplicate data in other columns (I'm using
Orders just as an example here, so I don't have to explain the whole story). The question is about any one-to-N relationship.