copy table from sql server 2005 to oracle

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.

jcpwAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

nmcdermaidCommented:
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!

0
David ToddSenior DBACommented:
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
0
jcpwAuthor Commented:
nmcdermaid:

I hear that! I just have to work with what's here...
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

jcpwAuthor Commented:
dtodd:

that results in an error:

OLE/DB provider returned message: ORA-00942: table or view does not exist
0
jcpwAuthor 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.
0
nmcdermaidCommented:
>> 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.
0
nmcdermaidCommented:
>> 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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vee_ModCommented:
Force accepted.
Vee_Mod
Community Support Moderator
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.