Inserting Across Databases (SQL)

Last night, I got an email  from someone needing to insert records from one database to another without exporting and importing or writing an SSIS package or SQL Server Integration Services package.  This is something that is done easily in SQL.

The coding to accomplish this task is just slightly different from Tip 8: SQL INSERT INTO SELECT Statement.  Below I will use you the same 2 pieces of code I used in Tip 8, but will add the extra code to account for the separate databases.

Example:

Database1 contains tblOriginal (Table 1) and houses the original data we wish to move to tblTarget (Table 2) located in Database2.  For this example, we are assuming these two tables contain identical column names.

Here I will show you two different ways to accomplish this task.   

The first way will copy all of the columns from tblOriginal (Table 1) in Database1 to the tblTarget (Table 2) located in Database2.

INSERT INTO Database2.dbo.tblTarget
SELECT * FROM Database1.dbo.tblOriginal;

The second way will copy specific columns from tblOriginal (Table 1) located in Database1 to the tblTarget (Table 2) located in Database2.

INSERT INTO Database2.dbo.tblTarget
(Column1, Column2, Column5)
SELECT Column1, Column2, Column5
FROM Database1.dbo.tblOrigninal;

I hope this helps someone out a bit and saves them a bit of time. I know it helped me out greatly.

If you like this post and want to see more, follow me on my website www.chadcompton.com
Or if you prefer...