Tim

Footprints in the snow of a warped mind

SQL Server Delete all data

Where to find me

Flickr Icon  Twitter Icon  Linked In Icon  FaceBook Icon  Windows Live Alerts Butterfly  RSS 2.0 

The Site Doctor is Hiring!

Enjoy what you read here?
Think you can do better?
Join our team and get paid
up-to £35,000.
Apply now.

Tag Cloud

AJAX (4) Analysis (3) ASP (6) ASP.Net (59) Error Reporting (4) Web Service (2) WSDL (1) Atlas (2) Azure (1) Born In The Barn (1) Business (95) Business Start-up Advice (37) Client (17) Expanding Your Business (24) Recruitment (1) C# (25) Canoeing (4) Canoe Racing (5) Cheshire Ring Race (5) Racing (2) Training (4) Christmas (1) CIMA (1) Cisco (1) 7970G (1) CMS (1) Code Management (1) Cohorts (4) Commerce4Umbraco (1) Content (1) Content Management (1) Content Management System (1) CSS (4) dasBlog (5) DDD (2) DDDSW (1) Design (12) Icons (1) Development (28) Domain Names (1) eCommerce (13) Email (1) Employment (2) Festive Sparkle (1) General (39) Christmas (6) Fun and Games (11) Internet (22) Random (46) RX-8 (8) Git (2) Google (1) Google AdWords (1) Google Analytics (1) Hacking (1) Helpful Script (3) Home Cinema (2) Hosting (2) HTML (3) IIS (11) iPhone (1) JavaScript (5) jQuery (2) LINQPad (1) Marketing (6) Email (1) Multipack (1) MVC (3) Networking (3) Nintendo (1) Nuget (1) OS Commerce (1) Payment (1) Photography (1) PHP (1) Plugin (1) PowerShell (3) Presentation (1) Press Release (1) Productivity (3) Random Thought (1) Script (2) Security (2) SEO (6) Server Maintenance (7) Server Management (12) Social Media (2) Social Networking (3) Experiment (1) Software (11) Office (5) Visual Studio (14) Windows (6) Vista (1) Source Control (2) SQL (13) SQL Server (19) Starting Something New (2) Statistics (2) Stored Procedure (1) Sublime Text 2 (1) SVN (1) TeaCommerce (1) Testing (2) The Cloud (1) The Site Doctor (140) Turnover Challenge (1) Twitter (3) uCommerce (20) Umbraco (37) 2009 (1) 2011 (1) Useful Script (6) Virtual Machine (1) Web Development (72) WebDD (33) Wii (1) Windows Azure (1) XSLT (1)

Blog Archive

Search

<October 2014>
SunMonTueWedThuFriSat
2829301234
567891011
12131415161718
19202122232425
2627282930311
2345678

Recent Comments

Blog Archive

Various Links

Google+

Blogs I Read

[Feed] Google Blog
Official Google Webmaster Central Blog
[Feed] Matt Cutts
Gadgets, Google, and SEO
[Feed] Ol' Deano's Blog
My mate Dean's blog on my space, equally as random as mine but not off on as much of a tangent!
[Feed] Sam's Blog
Sam is one of my younger brothers studying Product Design and Manufacture at Loughborough, this is his blog :) Enjoy!

Disclaimer
The opinions expressed herein are my own personal opinions and do not represent my employer's view in any way.

newtelligence dasBlog 2.2.8279.16125

Send mail to the author(s) Email Me (Tim Gaunt)

© 2014 Tim Gaunt.

Sign In

    # Tuesday, April 17, 2007

    SQL Server Delete all data

    Tuesday, April 17, 2007 4:18:54 PM (GMT Daylight Time, UTC+01:00)

    I expect many people already know about this technique but I wanted to share it with those that don’t. The other day I needed to remove all data from a database before importing data from another database. I usually use DTS to copy the data across but knew that the database (one test) had conflicting ids so I decided deleting all the data out of the test database would be the best way to ensure all data’s up to date.

    I found this useful little set of SQL at: http://sqljunkies.com/WebLog/roman/archive/2006/03/03/18386.aspx, there are two solutions propsed within the post and comments so here they both are:

    Delete the data without resetting the identities

    -- disable referential integrity
    EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
    GO

    EXEC sp_MSForEachTable 'DELETE FROM ?'
    GO
    -- enable referential integrity again
    EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
    GO

    Delete the data and reset the identities

    -- disable referential integrity
    EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL' 
    GO 

    EXEC sp_MSForEachTable 'TRUNCATE TABLE ?' 
    GO 

    -- enable referential integrity again 
    EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL' 
    GO
     

    Don't forget to follow me on Twitter.