Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
wawansur
Starting Member
44 Posts |
 Posted - 2009-07-30 : 03:27:33
|
| i want truncate the table, but it can't run for tablewhich have reference to other table.any suggest?Rgds |
|
|
senthil_nagore
Master Smack Fu Yak Hacker
1007 Posts |
Posted - 2009-07-30 : 03:29:38
|
| 1.Delete the records in parent table and truncate the child table (Advisable).2.Remove the reference and truncate the table.Senthil.C------------------------------------------------------[Microsoft][ODBC SQL Server Driver]Operation canceledhttp://senthilnagore.blogspot.com/ |
 |
|
|
rajdaksha
Aged Yak Warrior
595 Posts |
Posted - 2009-07-30 : 04:03:26
|
quote:
Originally posted by wawansur
i want truncate the table, but it can't run for tablewhich have reference to other table.any suggest?Rgds
Hiwe need to take care of identity values & triggers is there in table.It's depends.You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint; instead, use DELETE statement without a WHERE clause.It's cannot activate the triggers.The counter used by an identity for new rows is reset to the seed for the column. If you want to retain the identity counter, use DELETE instead.-------------------------R.. |
|
|
|
bklr
Master Smack Fu Yak Hacker
1693 Posts |
Posted - 2009-07-30 : 06:12:14
|
if u want to truncate the table first nocheck the constraint and truncate the required tables and check the constraint script for nocheck and check constraints SELECT 'ALTER TABLE ' + object_name(parent_obj) + ' NOCHECK CONSTRAINT ' + name FROM sysobjects WHERE xtype = 'F' AND parent_obj = OBJECT_ID('tbl_pms_screens') ORDER BY object_name(parent_obj) --Copy and paste results into query window and run. This disables all foreign keys. SELECT 'ALTER TABLE ' + object_name(parent_obj) + ' WITH CHECK CHECK CONSTRAINT ' + name FROM sysobjects WHERE xtype = 'F' AND parent_obj = OBJECT_ID('tbl_pms_screens') ORDER BY object_name(parent_obj) |
|
|
|
|
|
|
truncate