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

sql-server oracle tsql linked-server openquery
Meher Jebali picture Meher Jebali · Jan 7, 2016 · Viewed 7.3k times · Source

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

Slawomir Cieslinski picture Slawomir Cieslinski · Sep 4, 2018

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;

Related questions

Is there a combination of "LIKE" and "IN" in SQL?

In SQL I (sadly) often have to use "LIKE" conditions due to databases that violate nearly every rule of normalization. I can't change that right now. But that's irrelevant to the question. Further, I often use conditions like WHERE something …

Read More
What is the Oracle equivalent of SQL Server's IsNull() function?

In SQL Server we can type IsNull() to determine if a field is null. Is there an equivalent function in PL/SQL?

Read More
How do I declare and use variables in PL/SQL like I do in T-SQL?

In Sql Server, often times when I'm testing the body of a stored procedure, I copy the body into SSMS, DECLARE the variables at the top of the page, set them to some sample values, and execute the body as-is. …

Read More