Select into statement where source is other database

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 ?

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

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

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

If you want to insert explicit values into a IDENTITY field then you can use SET IDENTITY_INSERT table OFF:

CREATE TABLE dbo.Destination
(
    Id INT IDENTITY(1,1) PRIMARY KEY
    ,Name NVARCHAR(100) NOT NULL
);

INSERT  dbo.Destination
VALUES  ('A'), ('B'), ('C');
GO

SET IDENTITY_INSERT dbo.Destination ON;

INSERT  dbo.Destination(Id, Name)
SELECT  T.Id, T.Name
FROM    (VALUES (10,'D'), (11,'E')) AS T(Id, Name); --or SourceDB.Schema.SourceTable

SET IDENTITY_INSERT dbo.Destination OFF;
GO

INSERT  dbo.Destination
VALUES  ('????????');
GO

SELECT  *
FROM    dbo.Destination

DROP TABLE Destination;

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.

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

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

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

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

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

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

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.