SQL 从两个不同的服务器数据库 INSERT INTO
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14153657/
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
INSERT INTO from two different server database
提问by fiberOptics
I am trying to copy the data of testdabse.invoicetable to basecampdev.invoicetable.
testdabseis a local database while basecampdevis in the server.
我正在尝试将testdabse.invoice表的数据复制到basecampdev.invoice表。
testdabse是本地数据库，而basecampdev在服务器中。
My query for copying data to another table doesn't work, it says
我的将数据复制到另一个表的查询不起作用，它说
Invalid object name 'basecampdev.dbo.invoice'.
I've been reading this documentationbut find it hard to follow and understand.
我一直在阅读此文档，但发现很难遵循和理解。
These are the information given from the server
这些是服务器提供的信息
Server type: Database Engine
Server name: server.database.windows.net (this is not the real name)
Authentication: SQL Server Authentication
Login: myusername
Password: mypassword
How can I connect to the server so that I would be able to run this query
如何连接到服务器以便我能够运行此查询
INSERT INTO [basecampdev].[dbo].[invoice]
([InvoiceNumber]
,[TotalAmount]
,[IsActive]
,[CreatedBy]
,[UpdatedBy]
,[CreatedDate]
,[UpdatedDate]
,[Remarks])
SELECT [InvoiceNumber]
,[TotalAmount]
,[IsActive]
,[CreatedBy]
,[UpdatedBy]
,[CreatedDate]
,[UpdatedDate]
,[Remarks] FROM [testdabse].[dbo].[invoice]
Screen shot
截屏
回答by Simon Martin
It sounds like you might need to create and query linked database servers in SQL Server
听起来您可能需要在 SQL Server 中创建和查询链接的数据库服务器
At the moment you've created a query that's going between different databases using a 3 part name mydatabase.dbo.mytablebut you need to go up a level and use a 4 part name myserver.mydatabase.dbo.mytable, see this post on four part namingfor more info
目前，您已经创建了一个使用 3 部分名称在不同数据库之间进行的查询，mydatabase.dbo.mytable但您需要上升一个级别并使用 4 部分名称myserver.mydatabase.dbo.mytable，请参阅有关四部分命名的这篇文章以获取更多信息
edit
The four part naming for your existing query would be as shown below (which I suspect you may have already tried?), but this assumes you can "get to" the remote database with the four part name, you might need to edit your host file / register the server or otherwise identify where to find database.windows.net.
编辑
现有查询的四部分命名如下所示（我怀疑您可能已经尝试过？），但这假设您可以使用四部分名称“访问”远程数据库，您可能需要编辑您的主机文件/注册服务器或以其他方式确定在哪里可以找到database.windows.net。
INSERT INTO [DATABASE.WINDOWS.NET].[basecampdev].[dbo].[invoice]
([InvoiceNumber]
,[TotalAmount]
,[IsActive]
,[CreatedBy]
,[UpdatedBy]
,[CreatedDate]
,[UpdatedDate]
,[Remarks])
SELECT [InvoiceNumber]
,[TotalAmount]
,[IsActive]
,[CreatedBy]
,[UpdatedBy]
,[CreatedDate]
,[UpdatedDate]
,[Remarks] FROM [BC1-PC].[testdabse].[dbo].[invoice]
If you can't access the remote server then see if you can create a linked database server:
如果您无法访问远程服务器，请查看是否可以创建链接数据库服务器：
EXEC sp_addlinkedserver [database.windows.net];
GO
USE tempdb;
GO
CREATE SYNONYM MyInvoice FOR
[database.windows.net].basecampdev.dbo.invoice;
GO
Then you can just query against MyEmployeewithout needing the full four part name
然后你可以查询MyEmployee而不需要完整的四部分名称
回答by Blue Bamboo
The answer given by Simon works fine for me but you have to do it in the right sequence: First you have to be in the server that you want to insert data into which is [DATABASE.WINDOWS.NET].[basecampdev] in your case.
西蒙给出的答案对我来说很好，但你必须按照正确的顺序来做：首先，你必须在你想要插入数据的服务器中，其中 [DATABASE.WINDOWS.NET].[basecampdev] 在你的案件。
You can try to see if you can select some data out of the Invoice table to make sure you have access.
您可以尝试查看是否可以从 Invoice 表中选择一些数据以确保您具有访问权限。
Select top 10 * from [DATABASE.WINDOWS.NET].[basecampdev].[dbo].[invoice]
Secondly, execute the query given by Simon in order to link to a different server. This time use the other server:
其次，执行 Simon 给出的查询以链接到不同的服务器。这次使用其他服务器：
EXEC sp_addlinkedserver [BC1-PC]; -- this will create a link tempdb that you can access from where you are
GO
USE tempdb;
GO
CREATE SYNONYM MyInvoice FOR
[BC1-PC].testdabse.dbo.invoice; -- Make a copy of the table and data that you can use
GO
Now just do your insert statement.
现在只需执行插入语句。
INSERT INTO [DATABASE.WINDOWS.NET].[basecampdev].[dbo].[invoice]
([InvoiceNumber]
,[TotalAmount]
,[IsActive]
,[CreatedBy]
,[UpdatedBy]
,[CreatedDate]
,[UpdatedDate]
,[Remarks])
SELECT [InvoiceNumber]
,[TotalAmount]
,[IsActive]
,[CreatedBy]
,[UpdatedBy]
,[CreatedDate]
,[UpdatedDate]
,[Remarks] FROM MyInvoice
Hope this helps!
希望这可以帮助！
回答by Raja Sekhar
You cannot directly copy a table into a destination server database from a different database if source db is not in your linked servers. But one way is possible that, generate scripts (schema with data) of the desired table into one table temporarily in the source server DB, then execute the script in the destination server DB to create a table with your data. Finally use INSERT INTO [DESTINATION_TABLE] select * from [TEMPORARY_SOURCE_TABLE]. After getting the data into your destination table drop the temporary one.
如果源数据库不在链接服务器中，则不能直接将表从其他数据库复制到目标服务器数据库中。但一种可能的方法是，将所需表的脚本（带有数据的架构）临时生成到源服务器 DB 中的一个表中，然后在目标服务器 DB 中执行该脚本以使用您的数据创建一个表。最后使用 INSERT INTO [DESTINATION_TABLE] select * from [TEMPORARY_SOURCE_TABLE]。将数据放入目标表后，删除临时表。
I found this solution when I faced the same situation. Hope this helps you too.
当我遇到同样的情况时，我找到了这个解决方案。希望这对你也有帮助。
回答by Sagar Mahajan
USE [mydb1]
SELECT *
INTO mytable1
FROM OPENDATASOURCE (
'SQLNCLI'
,'Data Source=XXX.XX.XX.XXX;Initial Catalog=mydb2;User ID=XXX;Password=XXXX'
).[mydb2].dbo.mytable2
/* steps -
1- [mydb1] means our opend connection database
2- mytable1 means create copy table in mydb1 database where we want insert record
3- XXX.XX.XX.XXX - another server name.
4- mydb2 another server database.
5- write User id and Password of another server credential
6- mytable2 is another server table where u fetch record from it. */
回答by Serg
You can use CREATE SYNONYMto remote object.
您可以使用CREATE SYNONYM来远程对象。
