Select into a statement using another database as the source.

sql sqlbulkcopy sql-server sql-server-2008 sql-server-2012

Question

How can data be transferred from one database to another with the same table structure while maintaining the key identities?

I want to convert some data from a SQL Server 2008 database to Sql Server 2012 "Denali." I have identical tables, however I want to transfer the data from the old DB to the new "Denali" DB. There are several servers hosting the databases.

In light of this, I want

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

Has anybody had any ideas on how to make this workable?

1
6
9/6/2015 7:20:08 PM

Accepted Answer

Create a connected server, then specify it in your query. You may have to utilizeIDENTITY_INSERT and also

The SSMS-integrated SSIS components may also load data from a variety of sources, including XML, flat files, and local or distant servers.

2
9/26/2011 11:42:06 AM

Popular Answer

Had this issue today, and it wasn't resolved:( If both DBs are on the same server, you must use the fully qualified names of the databases. Remember to use the.dbo schema..

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


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow