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: Thursday, September 18, 2014 7:22 AM
Points: 52, Visits: 84
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: Thursday, December 11, 2014 1:51 AM
Points: 2,693, Visits: 4,755
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: Wednesday, December 10, 2014 10:49 AM
Points: 1,194, Visits: 791
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: Wednesday, December 10, 2014 10:49 AM
Points: 1,194, Visits: 791
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: Thursday, November 20, 2014 5:13 AM
Points: 1,131, Visits: 1,394
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: Tuesday, December 16, 2014 10:41 PM
Points: 132, Visits: 227
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: Thursday, November 20, 2014 5:13 AM
Points: 1,131, Visits: 1,394
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