SQL Server 2008: copying the contents of all table

Question

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

Answer 1

I use RedGate's Data compare it works really well comes in handy when trying to solve data issues.

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

Answer 2

You can use the export wizard in SSMS. Right-click on the source database, select Tasks/Export data and follow the steps. A little tedious for 200 tables, but it's free.

Answer 3

Get yourself Red-Gate SQL Data Compare - best tool for the job, hands down.

Update: if you can't or don't want to spend any money, but instead want to spend a lot of your time, you can of course do something like this 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, too.

Answer 4

Without spending $, you could use the SELECT ... INTO ... syntax -- providing the table specified in the INTO clause does not exist already in the target database:

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

But that won't migrate constraints, triggers, jobs, logins, roles, etc. If the databases are on different hosts, you can use a Linked Server instance to connect them and use four name notation to reference the remote instance.

As for dealing with ~200 tables, you'd need to use dynamic SQL to create the statement because you can't supply a table name as a variable in dynamic SQL. The list of tables can come from either SYS.TABLES or INFORMATION_SCHEMA.TABLES

Answer 5

If you don't care about the data in the 2nd database (you're not looking for a merge), you can back up your database, then restore it over the other one.

I can post screenshots to demonstrate how if you like.

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

Open management studio & connect, then go into backup database from Tasks menu:

Then click OK button to back up database (Note, picking a different back up directory such as C:\ may not be a bad idea so that it is not "in use" when trying to restore it later):

Select Restore for the 2nd database you wish to paste over:

Select the 1st database from the list, or browse to the file to backup from:

It may be neccessary to check this check box on the options tab when performing a restore over existing data:

Click OK and it should work.