Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

How Truncate statement ? Expand / Collapse
Author
Message
Posted Wednesday, October 20, 2010 9:05 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 9:38 PM
Points: 51, Visits: 80
Comments posted to this topic are about the item How Truncate statement ?
Post #1008144
Posted Wednesday, October 20, 2010 10:11 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 9, 2012 4:22 PM
Points: 188, Visits: 76
yikes... i chose error while truncating due to the fact the table wasn't named correctly in the truncate statement....
Post #1008160
Posted Wednesday, October 20, 2010 10:58 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 6:20 AM
Points: 2,649, Visits: 4,725
Good Question. One more point can be added to the answer.
You can truncate a table that has a foreign key that references itself

See the link to the BOL below
http://msdn.microsoft.com/en-us/library/ms177570.aspx



Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1008170
Posted Wednesday, October 20, 2010 11:25 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, August 21, 2014 5:29 AM
Points: 1,179, Visits: 786
Nice Question : )

Yes we can not use truncate on a table which is referenced by a FOREIGN KEY constraint

The below statement is true if it is not referenced with any FOREIGN KEY:

If the table contains an identity column, the counter for that column is reset to the seed value defined for the column. If no seed was defined, the default value 1 is used.
Post #1008175
Posted Wednesday, October 20, 2010 11:27 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, August 21, 2014 5:29 AM
Points: 1,179, Visits: 786
Nice Question : )

Yes we can not use truncate on a table which is referenced by a FOREIGN KEY constraint

The below statement is true if it is not referenced with any FOREIGN KEY:

If the table contains an identity column, the counter for that column is reset to the seed value defined for the column. If no seed was defined, the default value 1 is used.
Post #1008176
Posted Wednesday, October 20, 2010 11:51 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 7:14 AM
Points: 1,108, Visits: 1,371
Nice question.
Delete is working for CustomerMaster table but truncate throws an error. I would like to know why it is not working? Any specific reason?


Thanks
Post #1008184
Posted Thursday, October 21, 2010 12:37 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, December 11, 2013 7:12 AM
Points: 366, Visits: 455
me too, although i know the logic behind truncate
Post #1008198
Posted Thursday, October 21, 2010 12:38 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 9:45 PM
Points: 132, Visits: 212
it truncates CustomerDocuments table
but not CustomerMaster Error Message fk ref. fired while truncating CustomerMaster.

Post #1008199
Posted Thursday, October 21, 2010 12:38 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, February 10, 2014 7:34 AM
Points: 17, Visits: 22
The error you will get is: CostomerMast does not Exist!!!

The table is called CostomerMastER
Post #1008200
Posted Thursday, October 21, 2010 12:40 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 7:14 AM
Points: 1,108, Visits: 1,371
w.rooks (10/21/2010)
The error you will get is: CostomerMast does not Exist!!!

The table is called CostomerMastER


This was typo mistake.
If you change it to CustomerMaster still SQL throws an error:
Cannot truncate table 'CustomerMaster' because it is being referenced by a FOREIGN KEY constraint.


Thanks
Post #1008201
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse