Unable to truncate table - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Unable to truncate table

Author

Topic

goh2499
Starting Member

3 Posts

Posted - 2004-10-09 : 03:23:13

Is there a way to truncate so that I can delete duplicate values from a table that has a foreign key dependency.

current code:
CREATE PROCEDURE test_delete AS
select distinct customer_firstname, customer_lastname,
shipping_street_address1, shipping_street_address2,
shipping_address_city,shipping_address_zip

into #holding

from [dvd_r_us].[dbo].[customers]

truncate table [dvd_r_us].[dbo].[customers]

insert [dvd_r_us].[dbo].[customers]

select *
from #holding

drop table #holding

:end code

I am currently reciving:

Server: Msg 4712, Level 16, State 1, Procedure test_delete, Line 10
Cannot truncate table 'dvd_r_us.dbo.customers' because it is being referenced by a FOREIGN KEY constraint.

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2004-10-09 : 05:55:48

Just drop the FK at the start of your procedure and recreate it at the end.

Mark

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources