8

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 Customers and 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.

10

Here's a way that scales easily to three related tables.

Use MERGE to insert the data into the copy tables so that you can OUTPUT the old and new IDENTITY values into a control table and use them for related tables mapping.

The actual answer is just two create table statements and three merges. The rest is sample data setup and tear down.

USE tempdb;

--## Create test tables ##--

CREATE TABLE Customers(
    [Id] INT NOT NULL PRIMARY KEY IdENTITY,
    [Name] NVARCHAR(200) NOT NULL
);

CREATE TABLE Orders(
    [Id] INT NOT NULL PRIMARY KEY IdENTITY,
    [CustomerId] INT NOT NULL,
    [OrderDate] DATE NOT NULL,
    CONSTRAINT [FK_Customers_Orders] FOREIGN KEY ([CustomerId]) REFERENCES [Customers]([Id])
);

CREATE TABLE OrderItems(
    [Id] INT NOT NULL PRIMARY KEY IdENTITY,
    [OrderId] INT NOT NULL,
    [ItemId] INT NOT NULL,
    CONSTRAINT [FK_Orders_OrderItems] FOREIGN KEY ([OrderId]) REFERENCES [Orders]([Id])
);

CREATE TABLE Customers2(
    [Id] INT NOT NULL PRIMARY KEY IdENTITY,
    [Name] NVARCHAR(200) NOT NULL
);

CREATE TABLE Orders2(
    [Id] INT NOT NULL PRIMARY KEY IdENTITY,
    [CustomerId] INT NOT NULL,
    [OrderDate] DATE NOT NULL,
    CONSTRAINT [FK_Customers2_Orders2] FOREIGN KEY ([CustomerId]) REFERENCES [Customers2]([Id])
);

CREATE TABLE OrderItems2(
    [Id] INT NOT NULL PRIMARY KEY IdENTITY,
    [OrderId] INT NOT NULL,
    [ItemId] INT NOT NULL,
    CONSTRAINT [FK_Orders2_OrderItems2] FOREIGN KEY ([OrderId]) REFERENCES [Orders2]([Id])
);

--== Populate some dummy data ==--

INSERT Customers(Name)
VALUES('Aaberg'),('Aalst'),('Aara'),('Aaren'),('Aarika'),('Aaron'),('Aaronson'),('Ab'),('Aba'),('Abad');

INSERT Orders(CustomerId, OrderDate)
SELECT Id, Id+GETDATE()
FROM Customers;

INSERT OrderItems(OrderId, ItemId)
SELECT Id, Id*1000
FROM Orders;

INSERT Customers2(Name)
VALUES('Zysk'),('Zwiebel'),('Zwick'),('Zweig'),('Zwart'),('Zuzana'),('Zusman'),('Zurn'),('Zurkow'),('ZurheIde');

INSERT Orders2(CustomerId, OrderDate)
SELECT Id, Id+GETDATE()+20
FROM Customers2;

INSERT OrderItems2(OrderId, ItemId)
SELECT Id, Id*1000+10000
FROM Orders2;

SELECT * FROM Customers JOIN Orders ON Orders.CustomerId = Customers.Id JOIN OrderItems ON OrderItems.OrderId = Orders.Id;

SELECT * FROM Customers2 JOIN Orders2 ON Orders2.CustomerId = Customers2.Id JOIN OrderItems2 ON OrderItems2.OrderId = Orders2.Id;

--== ** START ACTUAL ANSWER ** ==--

--== Create Linkage tables ==--

CREATE TABLE CustomerLinkage(old INT NOT NULL PRIMARY KEY, new INT NOT NULL);
CREATE TABLE OrderLinkage(old INT NOT NULL PRIMARY KEY, new INT NOT NULL);

--== Copy Header (Customers) rows and record the new key ==--

MERGE Customers2
USING Customers
ON 1=0 -- we just want an insert, so this forces every row as unmatched
WHEN NOT MATCHED THEN
INSERT (Name) VALUES(Customers.Name)
OUTPUT Customers.Id, INSERTED.Id INTO CustomerLinkage;

--== Copy Detail (Orders) rows using the new key from CustomerLinkage and record the new Order key ==--

MERGE Orders2
USING (SELECT Orders.Id, CustomerLinkage.new, Orders.OrderDate
FROM Orders 
JOIN CustomerLinkage
ON CustomerLinkage.old = Orders.Id) AS Orders
ON 1=0 -- we just want an insert, so this forces every row as unmatched
WHEN NOT MATCHED THEN
INSERT (CustomerId, OrderDate) VALUES(Orders.new, Orders.OrderDate)
OUTPUT Orders.Id, INSERTED.Id INTO OrderLinkage;

--== Copy Detail (OrderItems) rows using the new key from OrderLinkage ==--

MERGE OrderItems2
USING (SELECT OrderItems.Id, OrderLinkage.new, OrderItems.ItemId
FROM OrderItems 
JOIN OrderLinkage
ON OrderLinkage.old = OrderItems.Id) AS OrderItems
ON 1=0 -- we just want an insert, so this forces every row as unmatched
WHEN NOT MATCHED THEN
INSERT (OrderId, ItemId) VALUES(OrderItems.new, OrderItems.ItemId);

--== ** END ACTUAL ANSWER ** ==--

--== Display the results ==--

SELECT * FROM Customers2 JOIN Orders2 ON Orders2.CustomerId = Customers2.Id JOIN OrderItems2 ON OrderItems2.OrderId = Orders2.Id;

--== Drop test tables ==--

DROP TABLE OrderItems;
DROP TABLE OrderItems2;
DROP TABLE Orders;
DROP TABLE Orders2;
DROP TABLE Customers;
DROP TABLE Customers2;
DROP TABLE CustomerLinkage;
DROP TABLE OrderLinkage;
  • OMG you saved my life. Could please add some more filter like 'only copy to Database 1 when Orders2 has more than 2 items' – Anh Bảy Jan 2 at 14:30
2

When I've done this in the past, I did it something like this:

  • Backup both databases.

  • Copy the rows you want to move from the first DB to the second into a new table, without an IDENTITY column.

  • Copy all child rows of those rows into new tables without foreign keys to the parent table.

Note: We'll refer to the above set of tables as "temporary"; however, I highly recommend you store them in their own database, and back that up as well when you're done.

  • Determine how many ID values you need from the second database for rows from the first database.
  • Use DBCC CHECKIDENT to shift the next IDENTITY value for the target table to 1 beyond what you need for the move. This will leave an open block of X IDENTITY values that you can assign to the rows being brought over from the first database.
  • Set up a mapping table, identifying the old IDENTITY value for the rows form the first DB, and the new value that they'll use in the second DB.
  • Example: You're moving 473 rows that will need a new IDENTITY value from the first database to the second. Per DBCC CHECKIDENT, the next identity value for that table in the second database is 1128 right now. Use DBCC CHECKIDENT to reseed the value to 1601. You will then populate your mapping table with the current values for the IDENTITY column from your parent table as old values, and use the ROW_NUMBER() function to assign the numbers 1128 through 1600 as the new values.

  • Using the mapping table, update the values in what's usually the IDENTITY column in the temporary parent table.

  • Using the mapping table, update the values that are usually foreign keys to the parent table, in all the copies of the child tables.
  • Using SET IDENTITY_INSERT <parent> ON, insert the updated parent rows from the temporary parent table into the second DB.
  • Insert the updated child rows form the temporary child tables into the second DB.

NOTE: If some of the child tables have IDENTITY values of their own, this gets quite complicated. My actual scripts (partially developed by a vendor, so I can't really share them) deal with dozens of tables and primary key columns, including some that weren't auto-increment numeric values. However, these are the basic steps.

I retained the mapping tables, post migration, which had the benefit of allowing us to find a "new" record based on an old ID.

It's not for the faint of heart, and must, must, must be tested (ideally multiple times) in a test environment.

UPDATE: I should also say that, even with this, I didn't worry overmuch about "wasting" ID values. I actually set up my ID blocks in the second database to be 2-3 values larger that what I needed, to try to ensure I wouldn't accidentally collide with existing values.

I certainly understand not wanting to skip hundreds of thousands of potential valid IDs during this process, especially if the process will be repeated (mine was ultimately run a grand total of around 20 times over the course of 30 months). That said, in general, one cannot rely on auto-increment ID values to be sequential without gaps. When a row is created and rolled back, the auto-increment value for that row goes away; the next row added will have the next value, and the one from the rolled back row will be skipped.

  • Thanks. I get the idea, basically pre-allocating a block of IDENTITY values, then manually alter the values in a set of temp tables until they match the destination, then insert. For my scenario though, the child table do have its IDENTITY column (I actually have to move three tables, with two 1-N relationship between them). This makes it quite complicated, but I appreciate the idea. – kevin May 1 '17 at 19:12
  • 1
    Are the child tables parents to yet other tables? That's when things get complicated. – RDFozz May 1 '17 at 19:19
  • Think like Customer-Order-OrderItem or Country-State-City. The three tables, when grouped together, are self-contained. – kevin May 1 '17 at 19:24
0

I am using a table from WideWorldImporters database which is the new sample database from Microsoft. In that way you can run my script as is. You can download a backup of this database from here.

Source table (this exist in sample with data).

USE [WideWorldImporters]
GO


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [Warehouse].[VehicleTemperatures]
(
    [VehicleTemperatureID] [bigint] IDENTITY(1,1) NOT NULL,
    [VehicleRegistration] [nvarchar](20) COLLATE Latin1_General_CI_AS NOT NULL,
    [ChillerSensorNumber] [int] NOT NULL,
    [RecordedWhen] [datetime2](7) NOT NULL,
    [Temperature] [decimal](10, 2) NOT NULL,
    [FullSensorData] [nvarchar](1000) COLLATE Latin1_General_CI_AS NULL,
    [IsCompressed] [bit] NOT NULL,
    [CompressedSensorData] [varbinary](max) NULL,

 CONSTRAINT [PK_Warehouse_VehicleTemperatures]  PRIMARY KEY NONCLUSTERED 
(
    [VehicleTemperatureID] ASC
)
)
GO

Destination table:

USE [WideWorldImporters]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [Warehouse].[VehicleTemperatures_dest]
(
    [VehicleTemperatureID] [bigint] IDENTITY(1,1) NOT NULL,
    [VehicleRegistration] [nvarchar](20) COLLATE Latin1_General_CI_AS NOT NULL,
    [ChillerSensorNumber] [int] NOT NULL,
    [RecordedWhen] [datetime2](7) NOT NULL,
    [Temperature] [decimal](10, 2) NOT NULL,
    [FullSensorData] [nvarchar](1000) COLLATE Latin1_General_CI_AS NULL,
    [IsCompressed] [bit] NOT NULL,
    [CompressedSensorData] [varbinary](max) NULL,

 CONSTRAINT [PK_Warehouse_VehicleTemperatures_dest]  PRIMARY KEY NONCLUSTERED 
(
    [VehicleTemperatureID] ASC
)
)
GO

Now doing the export without identity column value. Notice I am not inserting into the identity column VehicleTemperatureID and also not selecting from the same.

INSERT INTO [Warehouse].[vehicletemperatures_dest] 
            (
             [vehicleregistration], 
             [chillersensornumber], 
             [recordedwhen], 
             [temperature], 
             [fullsensordata], 
             [iscompressed], 
             [compressedsensordata]) 
SELECT  
       [vehicleregistration], 
       [chillersensornumber], 
       [recordedwhen], 
       [temperature], 
       [fullsensordata], 
       [iscompressed] [bit], 
       [compressedsensordata] 
FROM   [Warehouse].[vehicletemperatures] 

To answer the second of the question about FK constraints please see this post. Especially section below.

What you should do is save the SSIS package that the wizard creates, then edit it in BIDS/SSDT. When you edit the package you'll be able to control the order that the tables are processed in so you can process the parent tables then process the child tables when all the parent tables are done.

  • This only inserts data into one table. It does not addresses the question of how to preserve FK relationship when the new PK is not known before runtime. – kevin May 1 '17 at 18:31
  • 1
    There are already two tables in the question, with a relationship. And yes, I am exporting from both tables. (No offence, but not sure how you missed it...) – kevin May 1 '17 at 18:37
  • I did modify the answer. Please the addition at the end. – SqlWorldWide May 1 '17 at 18:41
  • @SqlWorldWide that question seems somewhat related but not identical. Which of the answers are you referring to as a solution to the issue here? – ypercubeᵀᴹ May 1 '17 at 18:45
  • edited the answer again. If you are doing it by using tsql script and not (ssis) you can implement the same concept. – SqlWorldWide May 1 '17 at 18:52

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.