We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

SQL 2000 insert into an oracle database

Netstore
Netstore asked
on
Medium Priority
286 Views
Last Modified: 2008-02-01
I have created a linked server to n oracle database which works fine i.e. I can pull data. However I want to insert data into this oracle table depending on rows returned from a SQL 2000 query:


1. select * from my table
2. insert results set into the oracle database table

How do I link these together using insert into openquery?
Comment
Watch Question

Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
insert into openquery(oracle_linked_server, 'select * from oracle_table where 1=0 ' )
  select * from mytable

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
AneeshDatabase Consultant
CERTIFIED EXPERT
Top Expert 2009

Commented:
INSERT INTO OPENQUERY( oracle , 'select Col1,Col2..from OracleTable) (Col1,Col2.. )
SELECT Col1,Col2..From myTable


Author

Commented:
Cheers !!! Much simpler than I thought, thanks for the quick response !!

Author

Commented:
Another quickie when I try and save this to a stored procedure I get an error 7405 - queries require the ANSI_NULLS and ANSI_WARNINGS to be set for the connection. Any ideas?
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON

CREATE PROCEDURE ....
AS.

etc
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.