Trying 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

SQL Server Development (2000)

Trying to truncate table

Author

Topic

label
Posting Yak Master

197 Posts

Posted - 2004-08-30 : 13:47:00

I've got a process where I want to reset three table by clearing the data from all and repopulating them:

truncate table cc_catalog_smc.dbo.cad_types
truncate table cc_catalog_smc.dbo.cad_versions
truncate table cc_catalog_smc.dbo.rf_cad_version

insert into cc_catalog_smc.dbo.cad_types 
	select cad_long_name, cad_small_name, cad_small_alias, cad_type from cad_types

insert into cc_catalog_smc.dbo.cad_versions
	select version_name from cad_versions

insert into cc_catalog_smc.dbo.rf_cad_version
	select * from rf_cad_version

The problem, my Cad Types and Cad Versions table both have Foriegn key relationships with the RF_Cad_Version table so it won't let me truncate. Do I have to lose my Foriegn key relationship just to truncate or is their another way.

X002548
Not Just a Number

15586 Posts

Posted - 2004-08-30 : 13:55:14

Nope...they gotta go...I was amazed at that...

Even If you get rid of all the dependant data, you still can't truncate.

How many rows are we talking about?

Brett

8-)

label
Posting Yak Master

197 Posts

Posted - 2004-08-30 : 14:11:55

quote:
Originally posted by X002548

Nope...they gotta go...I was amazed at that...

Even If you get rid of all the dependant data, you still can't truncate.

How many rows are we talking about?

Oh, maybe 50 or 60 at a time. I just hate that I can't enforce referential integrity and truncate as well. Oh well, guess I'll get rid of the relationships and pray Yukon fixes this limitation.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-30 : 14:18:05

Why is this a limitation?

Tara

label
Posting Yak Master

197 Posts

Posted - 2004-08-30 : 14:21:25

quote:
Originally posted by tduggan

Why is this a limitation?

Tara

Cause I had a automatic update/delete relationship in my primary key tables to ensure referential integrity. If I deleted a master record from the Cad Type table, I wanted it to automatically cascade delete any reference to that Cad ID. It's nice to have that in place as it ensures my data is good.

However, I'll now have to remove that relationship which also removes my ability to cascade delete and consequently, my ability to automatically ensure data integrity.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-30 : 14:22:58

But this is only true for truncate table. You should use delete instead.

Tara

label
Posting Yak Master

197 Posts

Posted - 2004-08-30 : 14:47:03

quote:
Originally posted by tduggan

But this is only true for truncate table. You should use delete instead.

Tara

If I delete my table, don't I then need to turn around, recreate the table, then recreate the indexes/relationships all over again?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-30 : 14:49:51

Don't delete the table. Delete the data:

DELETE FROM TableName1
DELETE FROM TableName2

instead of

TRUNCATE TABLE TableName1
TRUNCATE TABLE TableName2

Tara

label
Posting Yak Master

197 Posts

Posted - 2004-08-30 : 14:51:23

quote:
Originally posted by tduggan

Don't delete the table. Delete the data:

DELETE FROM TableName1
DELETE FROM TableName2

instead of

TRUNCATE TABLE TableName1
TRUNCATE TABLE TableName2

Gotcha. I'll try that. Thanks. :)

X002548
Not Just a Number

15586 Posts

Posted - 2004-08-30 : 14:57:33

You're thinking of DROP.....

and for 60 rows...DELETE should be fine

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources