CODEWITHSUNDEEP
sql-server
user773378
user773378

Reputation: 73

Remove all data from SQL Server database, then reenter data from another database?

I have an outdated database, but I would like to keep the data from this database in a separate version of the current working database. I created a copy of my current database, but it has all new data in it. Is there a way to remove all of this data and then import the data from the outdated database?

Upvotes: 5

Views: 2973

Answers (1)

Hasan Fahim
Hasan Fahim

Reputation: 3885

Try this to remove data:

-- disable all constraints
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

-- delete data in all tables
EXEC sp_MSForEachTable "DELETE FROM ?"

-- enable all constraints
exec sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

To import data:

  1. In Microsoft SQL Server Management Studio Express, expand the databases.
  2. Right-Click on the database containing objects you want to copy to another database
  3. Click on Tasks, then Generate Scripts... This will open up the Script Wizard, which is pretty much self-explanatory:
  4. Select the database (which should be pre-selected for you),
  5. Select Options (first time around you might just accept the default selections),
  6. Choose the Object Types you want to script (Stored Procedures, User Defined Functions, etc),
  7. Select the specific objects to be scripted,and finally specify where to output the script (choose Script to new Query Window for convenience).

Once completed, return to the Query Window with the scripted objects. Change the USE directive (the first line) to point to the destination database (e.g., USE [Northwind]), and execute the script.

Upvotes: 8

Related Questions