CODEWITHSUNDEEP
sql-serversql-server-2008
Scott DePouw
Scott DePouw

Reputation: 3889

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

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:

Upvotes: 1

Views: 2689

Answers (2)

Gerardo Grignoli
Gerardo Grignoli

Reputation: 15167

If the databases are in different servers: 

exec sp_addlinkedserver ServerA

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

Upvotes: 2

orka
orka

Reputation: 1328

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

Upvotes: 4

Related Questions