SQL Server Performance Forum – Threads Archive

Import into a table with PK

I want to import from a text file into a table with a Primary Key. One way is to drop the PK, then import data and then ALTER table and add the PK to the table. How it is possible to have the PK in the table and do the import? CanadaDBA

---

Is the PK an identity column by any chance? If so and your text file includes number you want to place in that column, then you would need to check the "Enable Identity Insert" option in DTS when you define the import. If it’s not an identity column, you shouldn’t have any trouble importing data with the primary key still on the table, unless the import violates the integrity of the key. In that case, you wouldn’t be able to re-add the key after the import either though. Make sense? MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

---

Thanks Derrick for your reply.
There is no data for the ID column in my text file. Assume there are 5 fields in each row of the text file and I have 6 columns (1 ID and 5 other columns) in my table. I can set the PK as an identity column. CanadaDBA

---

It will set itself. Make sure the "Enable Identity Insert" option is NOT checked, so it produces the identity field for you. In your transformation, make sure the PK column (or ID columns) is marked as <ignore>. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

---

Yes, it worked perfect.
I needed this to test how much it might optimize the import process. We have a DTS that drops the PK from the two temp tables and imports data and then adds the PK to the tables. I thought if I keep the PK and do the import, I might save time and run the DTS faster. But it seems I was wrong! Here is the results for importing two text files with 368007 and 63527 rows:
BEFORE: 95 minutes
AFTER: 118 minutes
[xx(]

CanadaDBA

---

For the fastest speeds on imports into new or empty tables, it is generally faster to drop the indexes, run the import, and recreate them. It can be dangerous and slower though if you are just adding new row though, and the table already has thousands, or even millions, of existing rows. <br /><br />It’s a fun test though, isn’t it? [<img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />]<br /><br />MeanOldDBA<br />[email protected]<br /><br />When life gives you a lemon, fire the DBA.

---

]]>