Insert into table with Openquery / Linkedserver

[coder_t2]

Hey guys, I run SQL Server on a local machine. But our main database is a Oracle database located elsewhere. I use openqueries a lot, but I need to do a insert statement on one of the tables. I am trying to figure out how to insert into a table onto the Oracle database through the openquery. I am trying to insert data from another table. Example,

insert into table1
select * from table2

I know insert openquery(myoracle, 'select * from table1') values ('10') works. But I need to copy a large amount of information from another table.
I tried insert openquery(myoracle,
insert into table1
select * from table2) and it does not work. Do you guys know how to make this work? Or maybe some workaround?

The reason I do the OQ way is because I use C# to call stored procedures from the SQL server database. Which, in turn, calls openqueries to get data from the Oracle database. I pull the small amount of information I need and then do whatever manipulation needed to create a report and export into excel. Thanks.

 

[Markbnj]

Here's a post from sqlservercentral that discusses the syntax for doing this using openquery.

http://www.sqlservercentral.com/Forums/Topic429546-102-1.aspx#bm691144

 

[Oyster]

Here's a discussion we had some time ago about a similar topic and task at hand: http://forums.anandtech.com/showthread.php?t=2049253&highlight=openquery.

If you have lots of data, you may want to test your solution first - you may run into the infamous connection timeout error.

 

[coder_t2]

I may have poorly worded my question. I want to insert data into an Oracle table from another Oracle table. Not from SQL Server. Although this is useful information that I could use in the future.

 

[coder_t2]

Nevermind I figured out how to do it with the information here. I just used a second openquery instead of a SQL Server Table. Thanks guys.

 

[Oyster]

Can you please post your solution? Thanks.

 

[coder_t2]

Sure.

insert into
openquery(myoracle, select col1, col2 from table1)
select * from openquery(myoracle, select col1, col2 from table2)

So what happens is I copy the data to SQL Server and then insert back into oracle. Albeit inefficient, but it works. I am currently trying to understand SSIS better to optimize the process, and use openquery less.