SQL Server Performance Forum – Threads Archive

Drop and recreate PK Identity

I am going to import large amount of data into an empty table. My table has some indexes as well as a PK Identity field. I need to keep the ID data in my imported records. Therefore, the import is quiet slow. I decided to drop the ID (or even the indexes and PK) and import data and re-enforce them on the table. I used EM to create the scripts to drop and recreate the Identity (and the indexes and PK). The EM’s script reccreates a TMP table and copies all the records from the original table into it and then drops the original table and renames the TMP table. I believe this may take long time. 1. Do I need to drop the indexes to speed up the import process?
2. Do I need to drop the Identity constraint?
3. Any sample code? thanks,
CanadaDBA

Personally I would import it into a staging table and from there, bulk insert into your destination table 1. you dont need to drop the indices, but usually it helps for bulk adhoc imports if theres lots of data being imported 2. no, you dont need to drop the constraint. assuming your data contains sequential id’s you can simply set IDENTITY_INSERT ON for that table. If you try this and your id has gaps, you may run into problems I dont really recall what happens here 3. No <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> Chappy

ad 1) Technically you don’t need to. But it will speed up things when you create the index after the import. ad 2) Don’t do so! BCP, BULK INSERT and straight INSERTs all have mechanisms to insert explicitely into an IDENTITY column. When you decide to define the IDENTITY property on that column after the import, you already noticed what EM is doing in such a case. And afaik, that is the "best" way to create the IDENTITY property on an existing column ad 3) yes, search the forum and/or the Google Groups. [<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />] – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Ich unterstÃ¼tze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)

]]>