How do you insert rows into an oracle database fro MS SQL Server 1

[howmnsk]

Can anybody tell me is it possible for a T-SQL program to insert or delete records from an ORACLE database. If this is possible, can you either provide an example or a hyperlink?

 

[ousoonerjoe]

I used to move SQL 7 data to Oracle 8i. The SQL sysntax is the same between both of them. INSERT, DELETE, SELECT, UPDATE all have the same standard syntax. Only when you use Oracle only or MS-SQL only calls will you run into trouble.

--------------------------------------------------
Bluto: What? Over? Did you say "over"? Nothing is over until we decide it is! Was it over when the Germans bombed Pearl Harbor? No!
Otter: Germans?
Boon: Forget it, he's rolling.
--------------------------------------------------

 

[PRPhx]

You can set up a linked server and do what you want. Look either at BOL or do a google search. In my last job we did what you want to do. It involved linking an MSSQL db and an Oracle db. We used a linked server and a trigger to move data both ways.

The MSSQL stores index info for images, and the Oracle was used to perform AP functions. We would scan invoices and po's. During the scanning process I would set up dummy values for the type of PO. Every 30 minutes, a trigger would kick off, sending data to the Oracle side. The po's would be processed, including the real po number, etc. Another trigger then would replace the dummy info on the MSSQL side with the real info. Generally it worked well.

 

[ousoonerjoe]

I guess the question needed to be asked here, will it be a one-time action, or is it something that needs to be done on a regular basis?

--------------------------------------------------
Bluto: What? Over? Did you say "over"? Nothing is over until we decide it is! Was it over when the Germans bombed Pearl Harbor? No!
Otter: Germans?
Boon: Forget it, he's rolling.
--------------------------------------------------

 

[howmnsk]

The inserting into Oracle would be done on a as needed basis during the day (M-F)