Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Drop and Recreate constraints Expand / Collapse
Author
Message
Posted Sunday, January 17, 2010 9:56 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, September 18, 2010 3:42 AM
Points: 12, Visits: 53
Hi,

I have an ssis package that does truncate insert of all the tables in a database. Before i do this, i execute alter scripts to drop all the constraints and recreate them later.

Is there any option other than Alter table drop constraint and Alter table add constraint scripts for each and every constraint?
Post #848876
Posted Monday, January 18, 2010 1:29 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 11:56 AM
Points: 2,127, Visits: 5,551
There are some tables that you can’t truncate. One of the conditions is that the table is not referenced in a foreign key constraint. Unfortunately even if you disable the constraint, you still can’t truncate the table. The only option is to drop the foreign key constraint and recreate it after the truncate operation. Notice that you should only do it with foreign keys constraints and not with all constraints as you wrote in your message. Also if the tables are not big tables, you can run delete statement instead of truncate.

Adi


--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #849009
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse