SQL Server 2008: copying the contents of all tables from one database to another database

Question

I have two databases with the same schema. I need to copy the contents of all tables from one database to another database. What is the best way to do this? There are about 200 tables. I have ssms.

Answer 1

If you do not need data in the second database (you are not looking for a merge), you can back up your database and then restore it on top of another.

I can post screenshots to demonstrate how you like.

~~~~~~~~ Screenshots added ~~~~~~~~~~~~~~~

Open the management studio and connect, then go to the backup database from the Tasks menu:

Then click OK to back up the database (Note: Choosing another backup, such as C: \, might be a bad idea to not use it when trying to restore it later):

Select Restore for the second database that you want to copy:

Select the 1st database from the list or go to the file for backup from:

You may need to check this box on the options tab when performing recovery from existing data:

Click "OK" and it should work.

Answer 2

You can use the export wizard in SSMS. Right-click the source database, select Tasks / Export Data, and do the following. A bit tiring for 200 tables, but it's free.

Answer 3

At no $ cost, you can use SELECT ... INTO ... syntax - providing the table specified in the INTO clause does not exist in the target database:

SELECT * INTO new_db.dbo.table FROM old_db.dbo.table 

But this will not lead to migration of restrictions, triggers, tasks, logins, roles, etc. If the databases are on different hosts, you can use the Linked Server instance to connect them and use the four names to refer to the remote instance.

As for working with ~ 200 tables, you will need to use dynamic SQL to create a statement because you cannot specify the table name as a variable in dynamic SQL. The list of tables can be obtained from SYS.TABLES or INFORMATION_SCHEMA.TABLES

Answer 4

Get Yourself Red-Gate SQL Data Compare is the best tool to work, hands down.

Update: if you cannot or do not want to spend money, but instead want to spend a lot of time, you can, of course, do something similar for each table:

 INSERT INTO TargetDatabase.dbo.YourTable1(list of fields) SELECT (list of fields) FROM SourceDatabase.dbo.YourTable1 

and then repeat this for the other 199 tables.

Answer 5

I use RedGate Data, this works well when you are trying to solve data problems.

http://www.red-gate.com/products/SQL_Data_Compare/