ansaurus

Question

Disable Primary Key and Re-Enable After SQL Bulk Insert

Answer 1

+5 A:

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.

Barry 2010-06-01 10:57:39

I was under the impression that disabling indexes before huge inserts and reenabling them afterwards would be a better process

Jon 2010-06-01 11:01:03

Yes for nonclustered indexes I believe it is. But by dropping and recreating a clustered index it may not be so beneficial. recreating the clustered index will then reorganise all of the data at page level?

Barry 2010-06-01 11:03:51

With a clustered index the data is actually stored inside of the index, so dropping the index is actually deleting the table.Thus dropping a clustered index means creating a new table without the index and copying all the data to this new table, then deleting the original and renaming the new. Quite a process.

Cobusve 2010-06-04 16:09:49

Answer 2

A:

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'

Manish Kumar 2010-10-22 14:59:43

ansaurus

tags:

views:

answers:

Disable Primary Key and Re-Enable After SQL Bulk Insert

related questions