We help IT Professionals succeed at work.

copy table from sql server 2005 to oracle

jcpw
jcpw asked
on
I have 2 servers: SQL Server 2005 and Oracle 10. In one step of a TSQL script I retrieve a result from Oracle. I do that with an openquery statement like this:
...
select *
into new_table
FROM openquery(ORACLE,'select * from new_table')
...
I want to now change the script so that new_table now depends on a look-up table created in SQL Server. So the openquery will now look something like this:
...
{create new_table_lookup}

select *
into new_table
FROM openquery(ORACLE,'select * from new_table a, new_table_lookup b where a.col_1 = b.col_1 ')
...
My question is: After I create new_table_lookup in SQL Server, can I get it over to Oracle right there in the same script? I want to avoid breaking up the script or having a manual step like "Now copy over new_table_lookup to Oracle".  Can openquery - or something similar - be used to push the table to Oracle in TSQL? Assume new_table_lookup will not be static, so it can't just be copied over on a one-time basis.

Comment
Watch Question

You can use OPENROWSET or OPENQUERY

Here an EE reference that shows you how to do it:

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_20650730.html


You may to have a think about yuor architecture, it sounds like there's data flying everywhere!

David ToddSenior Database Administrator
CERTIFIED EXPERT

Commented:
Hi,

Why can't you do something like this:
insert new_table
select *
FROM openquery(ORACLE,'select * from new_table') oq
left outer join new_table nt
  on oq.key = nt.key
where nt.key is null

Cheers
  David

Author

Commented:
nmcdermaid:

I hear that! I just have to work with what's here...

Author

Commented:
dtodd:

that results in an error:

OLE/DB provider returned message: ORA-00942: table or view does not exist

Author

Commented:
nmcdermaid:

That approach didn't work. I'm not positive, but I think all these examples assume that a table with the appropriate structure already exists on Oracle. Maybe I need to create some stored procedures on Oracle that create the structure for the table, or if already exists, truncate the table, then maybe the inserts approach will work.
>> these examples assume that a table with the appropriate structure already exists on Oracle

Definitely.

I assumed it was already there. You can definitely use that truncate method that ou mentioned. In fact  I would recommend that over the on-the-fly table creation.
>> After I create new_table_lookup in SQL Server, can I get it over to Oracle right there in the same script?

1. Use OPENQUERY to execute a CREATE TABLE script in the oracle database
2. Use INSERT INTO OPENROWSET to transfer data from SQL Server to Oracle

This can be put in the same script.

Your script at point 1 should check for table existence and drop it if it exists first.

Commented:
Force accepted.
Vee_Mod
Community Support Moderator

Explore More ContentExplore courses, solutions, and other research materials related to this topic.