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

i importing data old db new one. 1 of tables has on 30 million rows, have obtain same pk values old db. after migration, have develop stored procedures inserting data table. question is: should set pk (after migration) identity or when insert data, find max value pk , increment 1, insert? if should use first approach, how can i? tried doing via management studio, fails due time out exception. in case should use second approach, thread safe , decrease performance?

thank you!

sincerely, turik

the recommended approach drop indices, including primary keys, when bulk loading data speeds load , reduces load on transaction log. however, need make sure add identity property new table prior load , use set identity_insert .... on allow insert old identity values.

for example, let's assume destination table:

create table dbo.yourtable(yourtableid int identity(1,1), somedata int) 

you need use identity_insert...on ensure can insert data source table:

set identity_insert dbo.yourtable on  --copy data source table insert dbo.yourtable (yourtableid, somedata) select 1,1 union select 2,2 

after have migrated data, need witch identity_insert off again:

set identity_insert dbo.yourtable off 

add primary key:

alter table dbo.[yourtable] add constraint pk_yourtable_yourtableid primary key clustered (yourtableid)  

and reseed primary key reseed value being equal current maximum pk value

dbcc checkident ('[yourtable]', reseed, 2) 

after running command, record inserted value of 3 yourtableid

insert dbo.yourtable select 3