How to TRUNCATE tables data if table is having Foreign keys

Bandi
Posted by Bandi under Sql Server category on | Points: 40 | Views : 340
DELETE command logs the operation details on to log file for each record deletion.

what if the table is having huge number of records? Also, you doesn't want to log that DELETE operation ( to prevent log file memory consumption).

TRUNCATE TABLE command is very useful in this scenario.. It removes complete table data at a time, but not row-by-row....

So we can truncate table data if the row-by-row operation is not required for you.

But TRUNCATE TABLE command doesn't work if the target table is having child tables..

In this case, we need to drop FKs temporarily, then truncate data, and then re-create dropped foreign keys....

ALTER TABLE TableName DROP CONSTRAINT FK_Name
GO
TRUNCATE TABLE TableName
GO
ALTER TABLE ADD CONSTRAINT FK_Name .........
GO

Comments or Responses

Login to post response