How to select data from a SQL table and then Insert into a Oracle table?

Question

Hi guys,

I know we can create a Linked server to Oracle, but how do I select the data in a SQL table and insert into a Oracle table? What's the correct syntax?

Here is an example from MSDN, but the insert value was simply hard coded:

INSERT OPENQUERY (OracleSvr, 'SELECT name FROM joe.titles')
VALUES ('NewTitle');

Thank you! :)

---

Joe

Answer 1

Try as below

insert into openquery(OracleSvr, 'select Col1,Col2,Col3,col4 from Oracle_Table')
select Col1,Col2,Col3,Col4 from SQL_Server_Table;

---

RaghuM

Answer 2

Hi,

 

EXEC sp_addlinkedserver 
 'OracleLinkedServer', 'Oracle', 
 'MSDAORA', 'OracleServer' 

INSERT INTO OracleLinkedServer.DBName.schemaname.TableName (Name, GroupName)
VALUES (N'Public Relations', N'Executive General and Administration');
GO

for more informations follow below links.

http://msdn.microsoft.com/en-us/library/dd776381.aspx

 

http://www.sqlmag.com/article/sql-server/setting-up-an-oracle-linked-server49687

 

Regards,

 

Balwant.

---

Failure in Life is failure to try... PGDCA-98(South Gujarat University),MCTS (SQL Server 2005)

Answer 3

Thanks for such quick responses!

---

Joe