Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

sql-server

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?

like image 292
user773378 Avatar asked Jun 08 '11 15:06

user773378


People also ask

How do I restore a database from one instance to another?

Connect to the appropriate instance of the SQL Server Database Engine, and then in Object Explorer, select the server name to expand the server tree. Right-click Databases, and then select Restore Database. The Restore Database dialog box opens. Select the database to restore from the drop-down list.

How do I clear an entire SQL database?

Using SQL Server Management StudioIn Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that instance. Expand Databases, right-click the database to delete, and then click Delete. Confirm the correct database is selected, and then click OK.

How do you backup and restore SQL database from one server to another?

Backup and restore SQL database from one server to anotherLaunch SQL Server Management Studio and connect to the instance you want to backup. Then right-click the specific database, choose Tasks > Back Up. 2. Make sure the backup type is full backup, and then select a destination.


1 Answers

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.

like image 80
Hasan Fahim Avatar answered Sep 28 '22 16:09

Hasan Fahim