SQL Server 2008: How to Copy Data from Table in DB1 to Table in DB2?

Question

I have two identical databases on the same server. During a deployment process, I have data in tables in database A that need copied over to the tables in database B. What is the easiest way to programmatically accomplish this task?

EDIT:

• Tables do have identity columns.
• There are tables with foreign key constraints, so insert order is important.
• All of the rows will need to be copied. As far as I'm aware, this will always be the case.

Answer 1

Assuming that the tables don't have identity columns and belongs to the default (dbo) schema, try the TSQL insert query below;

Insert Into DatabaseB.dbo.DestinationTable
Select * From DatabaseA.dbo.SourceTable

If you have an identity column then execute statements below

    SET IDENTITY_INSERT DatabaseB.dbo.DestinationTable ON
GO

        Insert Into DatabaseB.dbo.DestinationTable
        Select * From DatabaseA.dbo.SourceTable
GO

    SET IDENTITY_INSERT DatabaseB.dbo.DestinationTable OFF
GO

Answer 2

If the databases are in different servers:

exec sp_addlinkedserver ServerA

Insert Into DatabaseB.dbo.DestinationTable
Select * From ServerA.DatabaseA.dbo.SourceTable