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 (94) Business Start-up Advice (36) 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 (27) Domain Names (1) eCommerce (12) 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 (5) Vista (1) Source Control (2) SQL (11) 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 (139) Turnover Challenge (1) Twitter (3) uCommerce (17) Umbraco (36) 2009 (1) 2011 (1) Useful Script (4) Virtual Machine (1) Web Development (72) WebDD (33) Wii (1) Windows Azure (1) XSLT (1)

Blog Archive

Search

<April 2014>
SunMonTueWedThuFriSat
303112345
6789101112
13141516171819
20212223242526
27282930123
45678910

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