Turik Mirash February 2016

insert into a big table where the PK is not an identity

I am importing data from an old DB into a new one. One of the tables has over 30 million rows, I have to obtain the same PK values from the OLD DB. After the migration, I have to develop stored procedures for inserting data into that table. My question is: should I set the PK (after migration) to be an Identity or when I insert data, find the Max value for PK and increment it by 1, then insert? If I should use the first approach, how can I? I tried doing it via Management Studio, but it always fails due to a time out exception. In case I should use the second approach, is it thread safe and does it decrease the performance?

Thank you!

Sincerely, Turik

Answers


Jaco February 2016

The recommended approach is drop all indices, including primary keys, when bulk loading data as it speeds up the load and reduces the load on the transaction log. However, you need to make sure you add the IDENTITY property to the new table prior to the load and use SET IDENTITY_INSERT .... ON to allow you to insert your old identity values.

For this example, let's assume this your destination table:

CREATE TABLE dbo.YourTable(YourTableId INT IDENTITY(1,1), SomeData INT)

You then need to use IDENTITY_INSERT...ON to ensure you can insert the data from your source table:

SET IDENTITY_INSERT dbo.YourTable ON

--copy data from source table
INSERT INTO dbo.YourTable
(YourTableId, SomeData)
SELECT 1,1
UNION
SELECT 2,2

After you have migrated the data, you need to witch the IDENTITY_INSERT off again:

SET IDENTITY_INSERT dbo.YourTable OFF

Add the primary key:

ALTER TABLE dbo.[YourTable] ADD CONSTRAINT PK_YourTable_YourTableID PRIMARY KEY CLUSTERED (YourTableID) 

And then reseed your primary key with the RESEED value being equal to the current maximum PK value

DBCC CHECKIDENT ('[YourTable]', RESEED, 2)

After running this command, this record will be inserted with a value of 3 for YourTableId

INSERT INTO dbo.YourTable
SELECT 3

Post Status

Asked in February 2016
Viewed 2,092 times
Voted 6
Answered 1 times

Search




Leave an answer