Have Geek Brain

Replace one database table with another

I have 2 databases with the same tables. DBProduction and DBLocal both have testTable. How can I replace testTable DBProduction with DBLocal? There are some new lines and some updated lines in the testTable from DBLocal.

Using SQL Server 2008

+2


a source to share


1 answer


If they are on the same server and have the same table structure, you can 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 can run the truncate statement as above, then right-click 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

then use the Data Import Wizard to import (and by default recreate) the testTable into DBProduction.

EDIT 2:



If you want to insert (without listing all columns) when the column is set to "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 temporarily ignores the seeding of IDs.

NTN

Barry

+3


a source






More articles:


All Articles