Truncate Table

Question

Truncate Table

Viewing 15 posts - 16 through 30 (of 32 total)

You must be logged in to reply to this topic. Login to reply

gmby SSCarpal Tunnel Points: 4511 More actions · Answer 1

good question. but for the life of me, i cannot come up with a reason to use this (nor anyone else where I work)- anybody?

jg

chgn01 SSCarpal Tunnel Points: 4420 More actions · Answer 2

BOL - Restrictions

You cannot use TRUNCATE TABLE on tables that:

•Are referenced by a FOREIGN KEY constraint. (You can truncate a table that has a foreign key that references itself.)

Do we have any chance to use a table that has a foreign key that references itself?

--------------------------------------
;-)“Everything has beauty, but not everyone sees it.” ― Confucius

chgn01 SSCarpal Tunnel Points: 4420 More actions · Answer 3

Thank you for the posting.

We have many different ways to approach it. Is it a good idea or a bad idea?

--------------------------------------
;-)“Everything has beauty, but not everyone sees it.” ― Confucius

db4breakfast Ten Centuries Points: 1097 More actions · Answer 4

gmby (3/28/2013)
good question. but for the life of me, i cannot come up with a reason to use this (nor anyone else where I work)- anybody?
jg

This scenario is often used for hierarchy. Stewart provided an example above.

--------------------------------------------------------------------------------------
Hai Ton
My Db4Breakfast blog.

Ken SSCoach Points: 17191 More actions · Answer 5

Outside of a hierarchy example, I can't think of when you would have a table foreign key reference to itself.

David Conn SSCertifiable Points: 6010 More actions · Answer 6

David Conn

SSCertifiable

Points: 6010

March 29, 2013 at 7:07 pm

#1602225

Thanks.

Dineshbabu Hall of Fame Points: 3220 More actions · Answer 7

Dineshbabu

Hall of Fame

Points: 3220

April 1, 2013 at 1:53 am

#1602390

Nice one..

--
Dineshbabu
Desire to learn new things..

Toreador SSChampion Points: 11389 More actions · Answer 8

KWymore (3/29/2013)
Outside of a hierarchy example, I can't think of when you would have a table foreign key reference to itself.

A piece of machinery that is no longer available because it has been superseded by a different piece of machinery, something like

create table MachineryParts(

PartID int identity not null primary key,

PartDescription nvarchar(100) not null,

IsAvailable bit not null,

ReplacedByPartID int constraint fkMachineryPartsReplacement foreign key references MachineryParts

)

webrunner SSC-Dedicated Points: 32073 More actions · Answer 9

Strange situation, but informative.

Thanks for the question.

- webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

angad.singh88 Old Hand Points: 300 More actions · Answer 10

+1 🙂

Angad

--Angad Singh
If I Stop Learning, I Cease to Be A SIKH !

demonfox SSCertifiable Points: 6289 More actions · Answer 11

nice and straightforwad - instinct worked for me ..:w00t:;-)

~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:

sqlnaive SSCoach Points: 17437 More actions · Answer 12

I got it correct. However assuming some other logic but anyways got corrected by the explanation and got one point (to my bonus) 🙂

jfgoude SSCrazy Points: 2586 More actions · Answer 13

not really a frequent usage, but an interesting question

B's-Data SSChasing Mays Points: 607 More actions · Answer 14

Good question but neither answer was correct. Granted you do NOT get an error but the truncate does not technically run either. If you go back and view the table data you will see that data has not been removed.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 15

bgdjwoods (4/30/2013)
Good question but neither answer was correct. Granted you do NOT get an error but the truncate does not technically run either. If you go back and view the table data you will see that data has not been removed.

Don't know what you are doing, but I added a couple of selects and it sure looks like the data is gone to me:

CREATE TABLE dbo.Test

(

Col_1 INT PRIMARY KEY

,Col_2 INT REFERENCES Test(Col_1)

,Col_3 INT IDENTITY

)

;

INSERT INTO dbo.Test VALUES (1,1);

INSERT INTO dbo.Test VALUES (2,2);

INSERT INTO dbo.Test VALUES (3,3);

GO

select * from dbo.Test;

go

TRUNCATE TABLE Test;

GO

select * from dbo.Test;

go

drop table dbo.Test;

go