Replace table of one database with other

Question

I have 2 databases with same tables. DBProduction and DBLocal both have testTable. How can i replace testTable of DBProduction with of DBLocal? There are some new rows inserted and some updated rows in testTable of DBLocal.

Using SQL Server 2008

Answer 1

If they are on the same server and have the same table structure you could do this:

Truncate Table DBProduction.dbo.testTable
Go

Insert Into DBProduction.dbo.testTable
Select * From DBLocal.dbo.testTable

If they are on different servers, you could run the truncate statement as above then right click the DBProduction and select Import Data. This will launch the wizard and import the data for you.

EDIT: I should also add that if the table structure is different, you should Drop Table DBProduction.dbo.testTable and then use the Import Data wizard to import (and by default recreate) the testTable in DBProduction.

EDIT 2:

If you want to insert (without listing all columns) when a column is set as Identity you need to do the following:

Set Identity_Insert DBProduction.dbo.testTable On

    Insert Into DBProduction.dbo.testTable
    Select * From DBLocal.dbo.testTable

Set Identity_Insert DBProduction.dbo.testTable Off

This will temporarily ignore the identity seeding.

HTH

Barry