Using openquery to insert into a linked oracle database

Using openquery to insert into a linked oracle database

Post by Rod Smit » Sat, 02 Sep 2000 20:43:56



Can some one send me or post an example of inserting data into an Oracle
database from a SQL 7 stored proceedure using OpenQuery. I've looked at the
knowledgebase, Transact SQL Help, and Books online but I still can't get it
to work.

This is what I've been using so far:

INSERT INTO OPENQUERY(oracle,'select
cust_acct,TO_CHAR(plan_id),wtn,service,prov from tbl_customer')
(cust_acct,TO_CHAR(plan_id),wtn,service,prov)
VALUES('Test','0','Test','Test','NS')

This is the error:

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '('.

Thanks ahead of time.
Rod Smith

 
 
 

Using openquery to insert into a linked oracle database

Post by Umachandar Jayachandra » Mon, 04 Sep 2000 07:04:01


Try:

INSERT INTO OPENQUERY( oracle ,
'select cust_acct, TO_CHAR(plan_id) as plan_id ,
        wtn , service , prov
from tbl_customer'
)
( cust_acct , plan_id , wtn , service , prov )
VALUES ( 'Test' , '0' , 'Test' , 'Test' , 'NS' )

    You didn't specify the column name correctly in the insert list. You
can't use a function name there & that too an Oracle function name.

--
Umachandar Jayachandran
SQL Resources at http://www.umachandar.com/resources.htm
(Please reply only to the newsgroup)

 
 
 

1. Using openquery to insert into a linked oracle database

Can some one send me or post an example of inserting data into an Oracle
database from a SQL 7 stored proceedure using OpenQuery. I've looked at the
knowledgebase, Transact SQL Help, and Books online but I still can't get it
to work.

This is what I've been using so far:

INSERT INTO OPENQUERY(oracle,'select
cust_acct,TO_CHAR(plan_id),wtn,service,prov from tbl_customer')
(cust_acct,TO_CHAR(plan_id),wtn,service,prov)
VALUES('Test','0','Test','Test','NS')

This is the error:

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '('.

Thanks ahead of time.
Rod Smith

2. Views

3. HOW DO I INSERT USING OPENQUERY INTO A ACCESS LINKED FILE

4. calculating age

5. Invalid atribute

6. Problem calling db2 linked server SP using OPENQUERY

7. Determine the Median

8. Using OPENQUERY to call SPROC on linked server

9. Problem using INSERT + OPENQUERY

10. Linked Server - Openquery ORACLE

11. Openquery - inserting data into Oracle

12. How to link to SQL Server using Oracle without using Oracle Gateway