Select into statement where source is other database

Question

How to copy data from one DB into another DB with the same table structure and keep the key identities?

I use Sql Server 2012 "Denali" and I want to copy some data from a Sql Server 2008 DB. The tables I have are exactly the same but I want the data from the old DB into the new "Denali" DB. The databases are on different servers.

So I want something like

USE newDB;
GO
SELECT *
INTO newTable
FROM OldDb.oldTable WITH (KEEPIDENTITY);
GO

Anyone have a suggestion to make this workable ?

Answer 1

Had this problem today and it didn't work :( You have to use fully qualified names of the databases if both DB's are on same server. Do not forget the .dbo schema.

Select  *
INTO [NEW DB].dbo.Lab_Tests
from [OLD DB].dbo.Lab_Tests

Answer 2

Configure a linked server and reference it in your query. You may need to use IDENTITY_INSERT as well.

The SSIS components built into SSMS can also load data from different sources (XML, flat file or a local/remote server).