February 1, 2010 at 10:40 pm
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...
February 2, 2010 at 6:12 am
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
February 2, 2010 at 5:13 pm
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
February 2, 2010 at 5:23 pm
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.
February 3, 2010 at 12:54 pm
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.
February 3, 2010 at 2:14 pm
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
February 3, 2010 at 8:43 pm
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
Change is inevitable... Change for the better is not.
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply