Best way to truncate an entire database under Sql Server 2005 Express?
Posted on 2008-10-07
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.