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

Saladin

Could you please clarify on dynamically from the table on local database – Nagpur

the data i want to insert into the table(on the linked server) should be select from a table on the local database – Saladin

#11153407 – Supplementary

It is my personal opinion that you are going about this backwards. The reason being is I don't like pushing to remotes from local. it's much easier to read from remote and do DML locally than to read locally and do DML remotely. What with varying permissions, syntax, etc. I always pull from remote and store locally. So on your Oracle instance you'd want to establish a connection to SQL Server and get the data. Not the other way around. Having said that, I don't know much at all about oracle. – Azal

OPENQUERY runs the given query on the linked server. So it won't work for querying from localDB to insert unless you want to build you entire SQL using dynamic sql and run it. Answer given by @Stephane below seems to be correct. – Deponent

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/

Parkins

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;

Tshombe

Answer 3

You should be able to use the linked server name as part of qualifying your table name, and then just do a normal insert into select:

INSERT INTO ORTEST.[dbname].[dbo].[reservation]
SELECT * from [dbname].[db].[reservation]

Deepset

Thank you for your answer, but that didn't work it must be remembered that the linked server is to an Oracle Database – Saladin