Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
CanadaDBA
583 Posts |
 Posted - 2005-09-08 : 13:24:44
|
| 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,Canada DBA |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-09-08 : 13:26:56
|
| Why not just use SET IDENTITY_INSERT OFF while the import is happening?How Enterprise Manager gets rid of the identity option is the only way it can be done. Enterprise Manager almost always makes the right decision when modifying the schema. That's why I have it generate the base script for me, then I copy it into Query Analzyer and modify it as needed.There is no ALTER TABLE that would do this.Tara |
 |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2005-09-08 : 13:27:33
|
| You're kinda all over the map here...and you forgot about Foreign Keys as well.How many tables are you importing to, and how much data?It sounds like you're just trying to refresh an entire database from another one.How about a dump and restore?Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx |
|
|
|
CanadaDBA
583 Posts |
Posted - 2005-09-08 : 14:08:39
|
| This table doesn't have any relation to any table. So, there is no FK. They are five tables (and each one has about 100,000 - 150,000 records) that I need to import data from Access tables. This is a daily work. Canada DBA |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-09-08 : 17:40:34
|
| Dropping the indexes will speed up the import. If your data contains the identity value already, then just use SET IDENTITY INSERT OFF.Tara |
|
|
|
|
|
|
Drop and recreate PK Identity