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 ?

score 12 · Answer 1 · answered Oct 18 '14 at 03:21

12

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

answered Oct 18 '14 at 03:21

TheTechGuy

16,560
16
115
136

score 4 · Accepted Answer · answered Sep 26 '11 at 11:42

4

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

answered Sep 26 '11 at 11:42

ta.speot.is

26,914
8
68
96

score 3 · Answer 3 · answered Sep 26 '11 at 11:17

3

If your both database on same server then you can do it like this way :

insert into newTable
select col1,col2  from OldDB.dbo.OldTable

answered Sep 26 '11 at 11:17

Upendra Chaudhari

6,473
5
25
42

they are unfortunately on different server. – Marthin Sep 26 '11 at 11:38
Then you need to try import/export functionality, but make sure that you need to disable all contraints first then import data and then enable all constraints. – Upendra Chaudhari Sep 26 '11 at 11:42

score 1 · Answer 4 · edited Jun 20 '20 at 09:12

There are certain limitation when copying the database from one server to another (remotely).

So you have to follow the steps to completely transfer the database from one server to another with same identities and along with constraints.

To say in short, Generate Script of database i.e. Right click database > Tasks > Generate Scripts > Select database > Mark these true: Triggers, Indexes, Primary & Foreign Keys and other if any. > Choose Object Types: Mark all true except for User (you can create users on new server later on manually). > Select all the Tables, SP, and other Objects > Script to New Window or Files (as you wish) > Finish.

Generate Script to 'Create Database' and run it on server.

Run the step 1. Script on the new server, check that the new database is selected in query window.

Disable all the constraints on new server > EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

I think using wizard is speedy so, Use Database Export Wizard from the old database server to transfer the data from old database server to new database server.

Again, Enable all the constraints on new server > EXEC sp_msforeachtable @command1="print '?'", @command2="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

PS: The version does not matter as you have to transfer the database supposing to be in current version in your case SQL Server 2008, and after copying the database you can change the version of database.

Database Properties > Select a page: 'Options' > Compatibility level > select the version from drop down.

score 0 · Answer 5 · edited Oct 31 '11 at 19:22

0

Assume you are now in the old database instance.
What you can do is:

Select * 
Into denali.dbo.newTable 
FROM oldTable

Its like copy oldTable (Structure and content) to newTable and export it to another database.

edited Oct 31 '11 at 19:22

Bo Persson

90,663
31
146
203

answered Oct 31 '11 at 07:47

suhaimi

1

Select into statement where source is other database

5 Answers5

Linked