SQL Server 2008: copying the contents of all tables from one database into another database
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.
5 Solutions collect form web for “SQL Server 2008: copying the contents of all tables from one database into another database”
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.
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.
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.
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
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.
I use RedGate’s Data compare it works really well comes in handy when trying to solve data issues.