insert into a table through openquery

[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.

 

[Slapo]

Look here for reference:
http://msdn.microsoft.com/en-us/library/ms188427.aspx

In short, try this:
insert into
openquery(myoracle, 'SELECT col1, col2 FROM table1')
SELECT col1, col2 FROM table2;

That should work if I read your post well and you want to insert data from a SQL Server table into a Oracle table.

 

[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.

 

[Slapo]

Sorry, I didn't get to look at my e-mails yesterday evening.
I think that unless you need to use variables from SQL Server, doing the copying directly in Oracle would be better. I'm not sure whether SQL Server's optimizer wouldn't decide it wants to pull the data inserted from one linked server's table into another one into its own temporary table (or just into its memory). That could be rather slow. If the tables are on different physical machines, then using linked servers is a rather comfortable and simple way of doing it, though.
This looks like a good reference, although I haven't tested it and I don't know which version of Oracle you use:
http://www.psoug.org/reference/insert.html
Why did your query:
insert into table1
select * from table2
fail? Were there any error messages?

 

[coder_t2]

Because no columns were returned. The error was something along the lines of SQL Server detects that no columns will be returned.