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.

  • Executing Queries Stored in a Column of a Table
  • Is it possible to automate SQL Server 2008 profiler?
  • SSIS error with xp_cmdshell
  • How can I find out what table is joined with a certain table?
  • TSQL Variable With List of Values for IN Clause
  • SQL: Like vs Contains - Different Results
  • 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.

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

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

    alt text

    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):

    alt text

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

    alt text

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

    alt text

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

    alt text

    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.

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

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.