using open query between oracle and sql server

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

using open query between oracle and sql server

Author

Topic

jung1975
Aged Yak Warrior

503 Posts

Posted - 2006-10-17 : 18:16:12

I need to execute the below statement using the open query. Both table are in Oracle. How can I do this?

insert into tableb
select consolidated_notes_num.nextval
,personal_num
,patient_note
,claim_num
,claim_page_num
from tablea;

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-10-17 : 22:25:22

You need to add oracle server as linked server and then you can use OPENQUERY or four-part name to access the server and do the inserts.

Below is the example taken from BOL:

EXEC sp_addlinkedserver 'OracleSvr', 
   'Oracle 7.3', 
   'MSDAORA', 
   'ORCLDB'
GO

SELECT *
FROM OPENQUERY(OracleSvr, 'SELECT name, id FROM joe.titles') 
GO

Harsh Athalye
India.
"Nothing is Impossible"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-10-18 : 09:39:14

or using DTS import data in sql server and work on that

Madhivanan

Failing to plan is Planning to fail

jung1975
Aged Yak Warrior

503 Posts

Posted - 2006-10-18 : 09:48:30

Can I run the below query in Open query statment ? if so , can you show me how I can put in the below statement in the open query?

insert into tableb
select consolidated_notes_num.nextval
,personal_num
,patient_note
,claim_num
,claim_page_num
from tablea;

jung1975
Aged Yak Warrior

503 Posts

Posted - 2006-10-18 : 12:28:18

I have a table b and I am trying to insert records comes from table a into table b using the above statement.

However, table b has the foreign key constraints ( c.claim_num, d.personal_num) against table c and table d, so if claim_num, personl_num that I am trying to insert into table b are not existed in the table c and table d , then error out . otherwise, insert the records...

since the whole proces of below statement will fail if there are foreign key violations.. I am trying to find the best way to generate the insert statement, so I can insert some of records wiouthout failing the whole process.. What should I do? how can i generate the dynamic sql statement?

insert into tableb
select consolidated_notes_num.nextval
,personal_num
,patient_note
,claim_num
,claim_page_num
from tablea;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources