SQL Server Performance Forum – Threads Archive

Ignore constraints

Hi All, I would like to find out if there is any command in SQL that will "ignore" any constraints on a table. Reason: I have a table with 100M rows of which only 5M are valid. My steps to get rid of the redundant rows are as follows: INSERT INTO <new temp table> only the valid records. (no constraints set up)
TRUNCATE original table
INSERT valid records back into the original table. (need to ignore constraints here…) I cannot rename the new table to be the original table because the table structure differs from client to client. The script has to be written in a very generic way to allow different table structures and constraints set up per client. The same script will run at all our clients. Thanks for your contributions in advance! Sanette
SQL Developer
JustEnough Software Corporation
Walking on water and coding of a spec is easy, as long as both are frozen – A friend

use NOCHECK CONSTRAINT to diable the constraint and then CHECK CONSTRAINT again check ALTER TABLE of BOL

As per me you should do it in below way. Select * into <new temp table> only the valid records (here u dont have to worry about contraints) TRUNCATE original table Alter original table with Nocheck option INSERT valid records back into the original table But before you do, backup your original table. Thanks and Regards Ravi K

What about BCP or BULK INSERT? By default, constraints are ignored during the load. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

]]>