Turning Off the referential Integrity

[manmaria]

I am doing to a conversion into Sql server and we have the schema now in MSS. I like to load the data first then want to turn oN the RI later. Is there a way I can accomplish this?

TIA

 

[manmaria]

Alright found it.

--To turn off
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'


--To turn on
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'

Any one where it keeps the relationships to turn back on?

Thanks

 

[jbenson001]

You will have to script out the relationships for each table. Drop them before the import and create them when you are done.

 

[manmaria]

Alright, figured out this too.

What is happening is whenever I run the turn OFF the SP, it changes the DDL to "NO" check constrainst to all the FKs and whenever I run the turn ON the SP, it changes the DDL to CHECK CONSTRAINT.
Very smart.