Insert into table on the Linked Server with data from local table

Question

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

Answer 1

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

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/

https://www.mssqltips.com/sqlservertip/4414/transferring-data-between-sql-server-2014-and-oracle-11g-databases/

Answer 2

Correct syntax

INSERT OPENQUERY(sql_server, 'SELECT a1,a2,a3 FROM database.schema.table_a') (a1, a2, a3)
SELECT b1, b2, b3 FROM database.schema.table_b;