SQL Select into 语句，其中源是其他数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7554157/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Select into statement where source is other database
提问by Marthin
How to copy data from one DB into another DB with the same table structure and keep the key identities?
如何将数据从一个 DB 复制到另一个具有相同表结构的 DB 并保留关键标识？
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.
我使用 Sql Server 2012“Denali”，我想从 Sql Server 2008 DB 复制一些数据。我拥有的表完全相同，但我希望将旧数据库中的数据放入新的“Denali”数据库中。数据库位于不同的服务器上。
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 ?
任何人都有建议使这个可行？
采纳答案by ta.speot.is
Configure a linked server and reference it in your query. You may need to use IDENTITY_INSERTas well.
配置链接服务器并在查询中引用它。您可能还需要使用IDENTITY_INSERT。
The SSIS components built into SSMS can also load data from different sources (XML, flat file or a local/remote server).
SSMS 中内置的 SSIS 组件还可以从不同来源（XML、平面文件或本地/远程服务器）加载数据。
回答by Hammad Khan
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.
今天遇到了这个问题，它没有用:(如果两个数据库都在同一台服务器上，你必须使用数据库的完全限定名称。不要忘记 .dbo 模式。
Select *
INTO [NEW DB].dbo.Lab_Tests
from [OLD DB].dbo.Lab_Tests
回答by Upendra Chaudhari
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
回答by Harsh Baid
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"
简而言之，生成数据库脚本，即右键单击数据库> 任务> 生成脚本> 选择数据库> 将这些标记为真：触发器、索引、主键和外键以及其他（如果有）。> 选择对象类型：除用户外全部标记为真（您可以稍后在新服务器上手动创建用户）。> 选择所有表、SP 和其他对象 > 脚本到新窗口或文件（如您所愿）> 完成。
生成脚本以“创建数据库”并在服务器上运行它。
在新服务器上运行步骤 1. 脚本，检查是否在查询窗口中选择了新数据库。
禁用新服务器上的所有约束 >
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"我认为使用向导很快，所以使用旧数据库服务器的数据库导出向导将数据从旧数据库服务器传输到新数据库服务器。
再次，启用新服务器上的所有约束 >
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.
PS：版本无关紧要，因为您必须传输假设在您的情况下为当前版本的数据库SQL Server 2008，并且复制数据库后您可以更改数据库的版本。
Database Properties > Select a page: 'Options' > Compatibility level > select the version from drop down.
数据库属性 > 选择一个页面：“选项” > 兼容性级别 > 从下拉列表中选择版本。
回答by suhaimi
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.
它就像将 oldTable（结构和内容）复制到 newTable 并将其导出到另一个数据库。
回答by Bogdan Sahlean
If you want to insert explicit values into a IDENTITYfield then you can use SET IDENTITY_INSERT table OFF:
如果要在IDENTITY字段中插入显式值，则可以使用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;
