SQL 将数据从一个数据库中的表复制到另一个单独的数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/940930/
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
Copy Data from a table in one Database to another separate database
提问by Gabe
Basically I have a two databases on SQL Server 2005.
基本上我在 SQL Server 2005 上有两个数据库。
I want to take the table data from one database and copy it to another database's table.
我想从一个数据库中获取表数据并将其复制到另一个数据库的表中。
I tried this:
我试过这个：
SELECT * INTO dbo.DB1.TempTable FROM dbo.DB2.TempTable
This didn't work.
这没有用。
I don't want to use a restore to avoid data loss...
我不想使用还原来避免数据丢失...
Any ideas?
有任何想法吗？
回答by Tom H
SELECT ... INTO creates a new table. You'll need to use INSERT. Also, you have the database and owner names reversed.
SELECT ... INTO 创建一个新表。您需要使用插入。此外，您还颠倒了数据库和所有者名称。
INSERT INTO DB1.dbo.TempTable
SELECT * FROM DB2.dbo.TempTable
回答by Mitchel Sellers
SELECT * INTO requires that the destination table not exist.
SELECT * INTO 要求目标表不存在。
Try this.
尝试这个。
INSERT INTO db1.dbo.TempTable
(List of columns here)
SELECT (Same list of columns here)
FROM db2.dbo.TempTable
回答by Aaron Alton
It's db1.dbo.TempTable and db2.dbo.TempTable
它是 db1.dbo.TempTable 和 db2.dbo.TempTable
The four-part naming scheme goes:
四部分命名方案是：
ServerName.DatabaseName.Schema.Object
服务器名称.数据库名称.架构.对象
回答by Peter Lange
Hard to say without any idea what you mean by "it didn't work." There are a whole lot of things that can go wrong and any advice we give in troubleshooting one of those paths may lead you further and further from finding a solution, which may be really simple.
很难说不知道你所说的“它没有用”是什么意思。有很多事情可能会出错，我们在对其中一条路径进行故障排除时给出的任何建议都可能使您越来越无法找到解决方案，这可能非常简单。
Here's a something I would look for though,
不过，这是我要寻找的东西，
Identity Insert must be on on the table you are importing into if that table contains an identity field and you are manually supplying it. Identity Insert can also only be enabled for 1 table at a time in a database, so you must remember to enable it for the table, then disable it immediately after you are done importing.
如果该表包含身份字段并且您手动提供它，则身份插入必须在您要导入的表上。Identity Insert 也只能在数据库中一次为 1 个表启用，因此您必须记住为该表启用它，然后在完成导入后立即禁用它。
Also, try listing out all your fields
另外，尝试列出您的所有字段
INSERT INTO db1.user.MyTable (Col1, Col2, Col3)
SELECT Col1, COl2, Col3 FROM db2.user.MyTable
回答by Deepak Kothari
We can three part naming like database_name..object_name
我们可以像database_name..object_name这样的三部分命名
The below query will create the table into our database(with out constraints)
下面的查询将在我们的数据库中创建表（没有约束）
SELECT *
INTO DestinationDB..MyDestinationTable
FROM SourceDB..MySourceTable
Alternatively you could:
或者你可以：
INSERT INTO DestinationDB..MyDestinationTable
SELECT * FROM SourceDB..MySourceTable
If your destination table exists and is empty.
如果您的目标表存在并且为空。
回答by Sabri Mevi?
Don't forget to insert SET IDENTITY_INSERT MobileApplication1 ONto the top, else you will get an error. This is for SQL Server
不要忘记插入SET IDENTITY_INSERT MobileApplication1 ON到顶部，否则你会得到一个错误。这是用于SQL Server
SET IDENTITY_INSERT MOB.MobileApplication1 ON
INSERT INTO [SERVER1].DB.MOB.MobileApplication1 m
(m.MobileApplicationDetailId,
m.MobilePlatformId)
SELECT ma.MobileApplicationId,
ma.MobilePlatformId
FROM [SERVER2].DB.MOB.MobileApplication2 ma
回答by Leon
Im prefer this one.
我更喜欢这个。
INSERT INTO 'DB_NAME'
(SELECT * from 'DB_NAME@DB_LINK')
MINUS
(SELECT * FROM 'DB_NAME');
Which means will insert whatsoever that not included on DB_NAMEbut included at DB_NAME@DB_LINK. Hope this help.
这意味着将插入任何未包含在DB_NAME但包含在DB_NAME@DB_LINK. 希望这有帮助。
回答by Sundeep Kumar Chowdary
INSERT INTO DB1.dbo.TempTable
SELECT * FROM DB2.dbo.TempTable
If we use this query it will return Primary key error.... So better to choose which columns need to be moved, like
如果我们使用这个查询，它将返回Primary key error.... 所以最好选择需要移动的列，比如
INSERT INTO db1.dbo.TempTable // (List of columns here)
SELECT (Same list of columns here)
FROM db2.dbo.TempTable
回答by Ghebrehiywet
This works successfully.
这工作成功。
INSERT INTO DestinationDB.dbo.DestinationTable (col1,col1)
SELECT Src-col1,Src-col2 FROM SourceDB.dbo.SourceTable
回答by David Basarab
Try this
尝试这个
INSERT INTO dbo.DB1.TempTable
(COLUMNS)
SELECT COLUMNS_IN_SAME_ORDER FROM dbo.DB2.TempTable
This will only fail if an item in dbo.DB2.TempTable is in already in dbo.DB1.TempTable.
仅当 dbo.DB2.TempTable 中的项目已在 dbo.DB1.TempTable 中时，这才会失败。
