SQL2K - import data THEN add? increment to primary key

[lmohr79]

I've got data from a MySQL database that had the primary key with "auto increment". Exported to a flat file for import.

Now am importing to SQL2K - problem is that of the original 353 records (just a tiny db, thank goodness!), there are only 342, with the first record ID = 2.

So I still need "ID" as the primary key, with Identity turned on to increment by 1.

Any ideas? I can easily use a DTS to import, but wondering how to set the increment "ON" after the import (if possible via Enterprise Mgr?) and how to get it to start incrementing with the next record as 354.

FYI - I've learned everything about MSSQL by the seat of my pants, so I'm always grateful for any help I get!!!!

 

[SQLSister]

If the table you are looking to import into has an identity filed already set then what you need to do to temporarily turn it oof to import historical data is use the set identity_insert command. Look up the syntax in BOL and you will see how it works. REmeber that while iddentity insert is set to on, no one else will be able to insert into the table and have an identity automatically set, so do not use this on a productin table unless you aresure no one else will be inserting records. But this is for exactly the type of thing you are talking about. Make sure to turn it off when you are done.

"NOTHING is more important in a database than integrity." ESquared

 

[lmohr79]

This makes sense - I did do some sleuthing prior to posting my question and came across some references to SET INDENTITY_INSERT.

I believe this will work - we're in the process of setting up the app that will use this new SQL db, so no one will be accessing it during development except myself and one other developer (who's within shouting distance in the office).

Thanks for the info!