Solved

Best way to truncate an entire database under Sql Server 2005 Express?

Posted on 2008-10-07
13
948 Views
Last Modified: 2012-08-13
Hello,

I have a project that involves regularly flushing data from 3 tables with the following structures:

Customer - all the usual fields + a primary key of customer id, PKCustomerID referencing FKCustomerID in the Orders table.

Orders - basic billing/shipping info and a primary key of orderid, PK OrderID, and a foreign key of FKCustomerID referencing PKCustomerID in the customer table.

OrderItems - order line item information, primary key of PKOrderLineItemID, foreign key of FKOrderID referencing PKOrderID in the Orders table.

Essentially, this is like an intermediary database that we import new information into on a daily basis, but flush the old data first. There's an application that sits on top the SQL Server database that allows creation/deletion of databases. Right now, I'm doing I'm flushing the existing data by recreating the database with the application that sits on top the DB, since I can't just truncate each table in turn due to foreign key relations, triggers, etc. but that's rather tedious/time consuming.

Is there an easier way to just empty all records from the database in general without having to drop/recreate the foreign keys etc?

Any insight greatly appreciated.

Thanks!
-James
0
Comment
Question by:jmoriarty
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
13 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 22660988
>>Is there an easier way to just empty all records from the database in general without having to drop/recreate the foreign keys etc?

No, not really.  You could truncate the tables, but you'd have to drop the FKeys first.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22661246
Well you can just do a delete. then wait. Can change the database to simple for a little while, then change it back - it will help a bit. But those foreign keys will prevent a truncate.
0
 
LVL 70

Assisted Solution

by:Éric Moreau
Éric Moreau earned 100 total points
ID: 22661410
script your database, drop it and run back your script
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 11

Expert Comment

by:aaronakin
ID: 22663973
It sounds like you are using these 3 tables to stage your data.  In my experience, you generally do not add constraints to staging tables.  Instead, you should verify and cleanse the data once it is in your staging tables and before it goes into the prod tables.  This means no FKs.  This also allows you an easy way to truncate the tables before you repopulate them.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22664964
Could also change the foreign keys to cater for cascading deletes. Won't help with truncate, but then only have to delete "parent" table.
0
 

Author Comment

by:jmoriarty
ID: 22665401
Scripting the DB is a great idea, I wish I'd thought of it before. What's the best way to go about that with MSSQL 2005 Express? Have to excuse my ignorance, my experience is primarily in MySQL. Cascading delete is a good option as well, that's something we could build into the import script even.

It's a little more complex than just using those tables to stage data unfortunately. We basically have a proprietary module for processing shipments that has no real "import" tool, so we export orders from our ecommerce system to import into the shipping app, but the only access we have is to the database itself, which contains all kinds of triggers, stored procedures, and the aforementioned constraints. So each day's orders are exported from the ecommerce database for import into the shipping application.

It's definitely not the best solution; Ideally I want to build an app to handle most of the data migration automatically, but there's other development projects of higher priority in the short term.

Thanks for the help folks.
0
 

Author Comment

by:jmoriarty
ID: 22665512
I misspoke slightly in the above. I know where to generate the scripts from within MS SQL Management Studio, but the issue is, one of the tables (Users) has an encrypted password field. What option do I need to set on the script generation wizard,  or what's the best way to script those type of objects?

Here's the error I'm getting:

TITLE: Microsoft.SqlServer.Express.Smo
------------------------------

Script transfer failed.

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Script+transfer+Transfer&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

Script failed for Server 'SQL_SERVER_BOX1'.  (Microsoft.SqlServer.Express.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Script+Server&LinkId=20476

------------------------------

Property TextHeader is not available for Trigger '[TG_Users_Change]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights.  The text is encrypted. (Microsoft.SqlServer.Express.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.PropertyCannotBeRetrievedExceptionText&EvtID=TextHeader&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------


Alternatively, is there a way to remove the encryption from a trigger/database object? it's a local application, no need for encryption other than plain text since everyone has the credentials by default anyways.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22665708
If it is just Customer, Order, Orderitems, then why do the entire database ?

Create script to create the Foreign Key constraints, and then use that to write a new script :
1) drop FK
2) truncate items, orders, customers (in that sequence)
3) create FK

0
 
LVL 51

Accepted Solution

by:
Mark Wills earned 400 total points
ID: 22666193
Something like :

alter table orderitems drop constraint FKOrderID;
alter table orders drop constraint FKCustomerID;
GO

truncate orderitems
truncate orders
truncate customer
GO

alter table orderitems add constraint FKOrderID Foreign Key (orderid)
references orders (orderid) on delete cascade;
GO

alter table orders add constraint FKCustomerID Foreign Key (customerid)
references customer (customerid) on delete cascade;
GO
0
 

Author Comment

by:jmoriarty
ID: 22667298
That's a very good point, mark_wills.  I was thinking of doing the whole thing because data does exist in the other tables, and could do a blanket drop/resubmit, but this is a much better option I think. The rest of the data is static anyway.  Going to give that a go in a short bit then. I shall report back.

Thanks again for the collaborative comments.
0
 

Author Comment

by:jmoriarty
ID: 22676930
Hi Folks,

Thanks for the help so far.  There were a lot more foreign key references than I was first aware of. References from other tables to these target tables basically. So I'm working through finding & adding those to the script in the short term. I'll report back on success or failure shortly.

0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22680125
this might help:

select * from information_schema.table_constraints where constraint_type like 'F%'
0
 

Author Comment

by:jmoriarty
ID: 22693150
That helped immensely actually. There was almost 25 keys referencing those tables that needed to be accounted for as well before they could be truncated. Got a good script working now though, in the fashion you suggested.

Thanks again!
-James
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

689 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question