Table truncation

Question

Table truncation

Digs

SSCertifiable

Points: 7333
More actions
February 1, 2010 at 10:40 pm

#386225

I have a bunch of tables joined via a SQL diagram (relationships).
I need to truncate transaction tables to reset to zero , in my test database.
I mean reset auto calcs fields to zero, remove test transactions, etc.
However I need to break the relationships up to run TRUNCATE TABLE.
Is there any easy way to truncate a table and restore relationships in the SQL 2005 diagram ? Any ideas how to do this easy...

Viewing 7 posts - 1 through 7 (of 7 total)

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

AndrewSQLDBA SSC-Insane Points: 21829 More actions · Answer 1

Easy way, no, no easy way.

You can create a SQL Script to do that. Drop the Constraint, truncate the table and create the Constraint again. Or you can script out the entire database, all objects, and then drop the current database, and then run the script to create everything new once again.

I suggest that next time, you create a copy of the database before loading it with data.

Andrew SQLDBA

SQLRNNR SSC Guru Points: 281344 More actions · Answer 2

AndrewSQLDBA (2/2/2010)
Easy way, no, no easy way.
You can create a SQL Script to do that. Drop the Constraint, truncate the table and create the Constraint again. Or you can script out the entire database, all objects, and then drop the current database, and then run the script to create everything new once again.
I suggest that next time, you create a copy of the database before loading it with data.
Andrew SQLDBA

Either method proposed will work. I recommend having the database backed up prior to changes as well. This would be the easier method.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Steve Jones - SSC Editor SSC Guru Points: 738907 More actions · Answer 3

Will the database still be correct for testing if you have orphaned rows?

Typically you truncate the children, then the parents. That way your relationships are intact.

tosscrosby SSCoach Points: 18044 More actions · Answer 4

CirquedeSQLeil (2/2/2010)
I recommend having the database backed up prior to changes as well. This would be the easier method.

Easier? I don't know. Safer, definitely.

-- You can't be late until you show up.

Jeffrey Williams SSC Guru Points: 90350 More actions · Answer 5

Steve Jones - Editor (2/2/2010)
Will the database still be correct for testing if you have orphaned rows?
Typically you truncate the children, then the parents. That way your relationships are intact.

Truncate won't work with the relationships defined - regardless of whether or not there is any data. What you could do is DELETE the data from the children up to the parent first - then reset the identity columns manually.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 6

As a side bar, you don't need to actually drop the constraints... you can disable them with ALTER TABLE and NO CHECK.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)