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

<August 2014>
SunMonTueWedThuFriSat
272829303112
3456789
10111213141516
17181920212223
24252627282930
31123456

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.

    Thursday, April 26, 2007 8:49:39 PM (GMT Daylight Time, UTC+01:00)
    Is this working for you? I tried this in my DTS task running on SQL Server 2000 SP4:

    EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
    GO
    truncate table dbo.Sku
    truncate table dbo.Orders
    truncate table dbo.OrdersOrderItem
    truncate table dbo.Inventory
    go
    EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
    GO

    But I still get the error:
    Step Error Source: Microsoft OLE DB Provider for SQL Server
    Step Error Description:Cannot truncate table 'dbo.Sku' because it is being referenced by a FOREIGN KEY constraint.
    roy
    Thursday, April 26, 2007 9:00:39 PM (GMT Daylight Time, UTC+01:00)
    Hi Roy,

    No that won't work, you just need to copy what I pasted, the ? is a place holder for the loop -in this case it is substituted with the table name.

    Hope that helps.

    Tim
    Thursday, April 26, 2007 9:33:32 PM (GMT Daylight Time, UTC+01:00)
    Hi Tim, I understood the ?'s purpose - not sure what you mean - am I misusing the '?' ? They look identical to me :) Unlike your example, I dont want to delete all the data in all tables. I'm doing this for test cases and I have already carefully created some data in other tables that I want to preserve. I just want to shove in some order data for a particular test. But perhaps it doesnt work unless I truncate all the tables? Why would it matter?
    Thanks,
    Roy
    Roy
    Thursday, April 26, 2007 9:37:05 PM (GMT Daylight Time, UTC+01:00)
    Hi Roy,

    My apologies, I wasn't paying attention closely enough, I've not tried putting the code into the DTS itself -I just ran it in the Management Studio first, I'll give your code ago and see what happens and let you know.

    Tim
    Sunday, May 27, 2007 3:02:10 AM (GMT Daylight Time, UTC+01:00)
    Hi, very good Article!
    example with delete works fine, but example with truncating of each table doesn't work properly. I think, that problem is in version of SQL server (and please, correct me if I'am wrong).

    When I execute:

    SET QUOTED_IDENTIFIER OFF
    GO
    EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
    GO
    EXEC sp_MSForEachTable 'TRUNCATE TABLE ?'
    GO
    EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
    GO
    SET QUOTED_IDENTIFIER ON
    GO

    I'll get error message:
    Msg 4712, Level 16, State 1, Line 1
    Cannot truncate table 'dbo.xxxx' because it is being referenced by a FOREIGN KEY constraint.


    I have:
    Microsoft SQL Server Management Studio Express 9.00.3042.00
    Microsoft Data Access Components (MDAC) 6.0.6000.16386 (vista_rtm.061101-2205)
    Microsoft MSXML 3.0 4.0 5.0 6.0
    Microsoft Internet Explorer 7.0.6000.16448
    Microsoft .NET Framework 2.0.50727.312
    Operating System Vista 6.0.6000

    I tried this example of disabling constraints:

    http://msdn.microsoft.com/msdnmag/code/Default.aspx?level=root&file=Cursor+to+disable+all+Foreign+Keys.sql&url=http%3a%2f%2fmsdn.microsoft.com%2fmsdnmag%2fissues%2f07%2f04%2fDataPoints%2fdefault.aspx

    but with same result :(

    similiar discussion of this problem is there:
    http://sqljunkies.com/WebLog/roman/archive/2006/03/03/18386.aspx
    Michal Kuritka
    Tuesday, April 15, 2008 7:43:15 AM (GMT Daylight Time, UTC+01:00)
    This has become easier with SQL 2005:

    EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
    GO
    EXEC sp_MSForEachTable 'ALTER TABLE ? DISABLE TRIGGER ALL'
    GO

    EXEC sp_MSForEachTable
    'BEGIN TRY
    TRUNCATE TABLE ?
    END TRY
    BEGIN CATCH
    DELETE FROM ?
    END CATCH;'

    EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
    GO
    EXEC sp_MSForEachTable 'ALTER TABLE ? ENABLE TRIGGER ALL'
    GO
    Wednesday, September 21, 2011 2:20:05 PM (GMT Daylight Time, UTC+01:00)
    hi, thank u for posting this but this deleted all my database diagrams please help me with this. i want to clean tables and also keep my database diagrams.thanks
    a
    Name
    E-mail
    (will show your gravatar icon)
    Home page

    Comment (HTML not allowed)  

    Live Comment Preview