问题描述
我正在使用SQL Server Express，创建了到Oracle数据库的链接服务器.
I'm working with SQL Server Express, I created a linked server to an Oracle database.
正如标题所示，我要将从本地表中选择的数据插入到链接服务器的表中.
As the title indicates, I want to insert data selected from a local table into a table at the Linked Server.
我尝试了许多查询，但没有一个能按照我的要求工作.
I tried many queries but no one of them worked as I want.
下面我使用的此查询有效，但仅适用于静态值，但我想从本地数据库中的表中动态插入数据.
This query below I used has worked, but only with static values, but I want to insert data dynamically from the a table on local database.
INSERT OPENQUERY (ORTEST, 'SELECT * FROM reservation')
VALUES (2, '2', 3);
推荐答案
正常情况下，它不适用于openquery.要写入远程表，必须在服务器级别设置链接服务器.这适用于oracle，除非您使用的SQL版本过旧.这是在服务器端设置链接服务器的方法:
It is normal that it doesn't work with an openquery. To write into a remote table, you must setup the linked server at your server level. This works with oracle, unless you have a sql version that is waaaay to old.Here is the way to setup the linkedserver at server side:
exec master.dbo.sp_MSset_oledb_prop 'ORAOLEDB.Oracle', N'AllowInProcess', 1
go
exec sp_addlinkedserver @server = 'MyOracleServer', @srvproduct = 'Oracle', @provider = 'OraOLEDB.Oracle', @datasrc = 'MyOracleLinkedServer'
go
exec master.dbo.sp_serveroption @server=N'MyOracleServer', @optname=N'rpc out', @optvalue=N'true'
go
sp_addlinkedsrvlogin @rmtsrvname = N'MyOracleServer', @useself = 'false', @locallogin = NULL, @rmtuser = 'myRemoteUser', @rmtpassword ='myRemotePassword'
go
然后您可以进行常规查询:
Then you can proceed with regular queries:
insert into [MyOracleServer]..[MyRemoteSchema].[MyRemoteTable](
[MyRemoteField1],
[MyRemoteField2]
)
select
t.Field1,
t.Field2
from
[dbo].[MyLocalTable] as t
如果您想了解更多详细信息，请点击以下两个链接: https://www.mssqltips.com/sqlservertip/4396/creating-a-sql-server-2014-linked-server-for-an-oracle-11g-database/
If you want to go in more details here are two links you want to see:https://www.mssqltips.com/sqlservertip/4396/creating-a-sql-server-2014-linked-server-for-an-oracle-11g-database/
这篇关于将本地表中的数据插入到链接服务器上的表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！