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 ?
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).
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