5

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);
jww
  • 97,681
  • 90
  • 411
  • 885
Meher Jebali
  • 199
  • 2
  • 3
  • 18
  • Could you please clarify on dynamically from the table on local database – TheGameiswar Jan 07 '16 at 14:03
  • the data i want to insert into the table(on the linked server) should be select from a table on the local database – Meher Jebali Jan 07 '16 at 14:13
  • https://stackoverflow.com/questions/11152907/how-to-insert-a-row-into-a-linked-server-table-too-many-prefixes-exception – CurseStacker Aug 30 '18 at 02:26
  • 3
    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. – BeardOfTriumph Aug 30 '18 at 15:25
  • 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. – Nachi Sep 03 '18 at 22:13

3 Answers3

5

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/

Stephane
  • 1,359
  • 1
  • 15
  • 27
4

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;
Slawomir Cieslinski
  • 625
  • 5
  • 14
0

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]
GendoIkari
  • 11,734
  • 6
  • 62
  • 104
  • 1
    Thank you for your answer, but that didn't work it must be remembered that the linked server is to an **Oracle** Database – Meher Jebali Jan 07 '16 at 14:23