CODEWITHSUNDEEP
sqlsql-server-2005sql-server-2008bulkinsert
Jon
Jon

Reputation: 40062

Disable Primary Key and Re-Enable After SQL Bulk Insert

I am about to run a massive data insert into my DB. I have managed to work out how to enable and rebuild non-clustered indexes on my tables but I also want to disable/enable primary keys as I believe this will speed up the insertion process.

NOTE: This is over numerous tables and so I assume I need some loop to get the primary key information and run the following to drop it but I'm not sure about recreating it:

ALTER TABLE Table1
DROP CONSTRAINT PK_Table1_Col1

Upvotes: 5

Views: 18335

Answers (2)

Manish Kumar
Manish Kumar

Reputation: 41

Run This :
//For disable all constraint of your all tables
exec sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
//Your insert query here ......................
//After Insert Enable all the constraint
exec sp_MSforeachtable 'ALTER TABLE ? CHECK CONSTRAINT ALL'

Upvotes: 4

codingbadger
codingbadger

Reputation: 44024

IIRC clustered indexes cannot be disabled as they govern where the actual data is stored in the pages.

I'm pretty sure you would have to drop the key and re-create it after your insert. Depending on the size of the tables, indexes and insert this may not save you any time.

Upvotes: 8

Related Questions