I'm looking for a way to temporarily turn off all DB's constraints (eg table relationships).
I need to copy (using INSERTs) one DB's tables to another DB. I know I can achieve that by executing commands in proper order (to not break relationships).
But it would be easier if I could turn off checking constraints temporarily and turn it back on after the operation's finish.
Is this possible?
You can disable FK and CHECK constraints only in SQL 2005+. See ALTER TABLE
ALTER TABLE foo NOCHECK CONSTRAINT ALL
or
ALTER TABLE foo NOCHECK CONSTRAINT CK_foo_column
Primary keys and unique constraints can not be disabled, but this should be OK if I've understood you correctly.
And, if you want to verify that you HAVEN'T broken your relationships and introduced orphans, once you have re-armed your checks, i.e.
ALTER TABLE foo CHECK CONSTRAINT ALL
or
ALTER TABLE foo CHECK CONSTRAINT FK_something
then you can run back in and do an update against any checked columns like so:
UPDATE myUpdatedTable SET someCol = someCol, fkCol = fkCol, etc = etc
And any errors at that point will be due to failure to meet constraints.
You can actually disable all database constraints in a single SQL command and the re-enable them calling another single command. See:
I am currently working with SQL Server 2005 but I am almost sure that this approach worked with SQL 2000 as well