Hi,

I need to read data from a table in SQLSERVER and write to a table in Oracle database directly. Can I do that?

I dont want to PRINT and HOLD the records, instead of that I need to write that directly to the Oracle table.

SQL SQLMSS SET SERVER MASS
SQL SQLMSS
SELECT * FROM MASS_Application;
TABLE FILE SQLOUT
PRINT *
-****Is there any way to write directly to Oracle table?
END
-EXIT

This message has been edited. Last edited by: Kerry,

I believe you can do this by writing a stored procedure in SQL Server. Check for OPENQUERY in Sqlserver help. You may get some idea.

Hope it helps, Good Luck.

I got a solution. Thanks!

Cyril,

1. If you just want to create table in Oracle with data, you just need:

TABLE FILE
PRINT ....

ON TABLE HOLD AS [your name of table] FORMAT ORACLE PERSISTENCE PERMANENT
END

2. If you already have table in Oracle - learn MODIFY.

Cyril, what was your solution?

Following is what I did:

-**********DEFINE THE ORACLE CONNECTION PARAMETERS
SET SQLENGINE=SQLORA
SQL SET SERVER connection
-RUN
SQL SET CONNECTION_ATTRIBUTES connection/userid,password
-RUN


-*******DEFINE AN ADAPTER FOR SQL SERVER
SQL SQLMSS SET SERVER XAP

-*********THE FOLLWING CODE WILL CREATE AN ORACLE TABLE NAMED STG1_ORG_LINKAGE
-*********FROM THE SQLSERVER TABLE NAMED ORG_LINKAGE
SQL SQLMSS
SELECT * FROM ORG_LINKAGE;
TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS STG1_ORG_LINKAGE FORMAT ORACLE
END

Hi Ingas,

Is it possible to modify/update and existing table?

Cyril, you can do this using MODIFY or MAINTAIN

quote:

Originally posted by Cyril Joy:
Hi Ingas,

Is it possible to modify/update and existing table?

Of course.

1. HOLD way (and "wrong" way)

Step 1. ON TABLE HOLD AS [my temporary table] FORMAT SQLORA
Step 2. run
SQL SQLORA
INSERT INTO [my existing table]
SELECT * FROM [my temporary table];
COMMIT; -- I'm not sure, but in Oracle you can COMMIT even if no modifications was done
END

2. MODIFY ( "right" way)
Cyril, you even do not have to learn MODIFY

Start DataManagement Console, draw your needed data flow, save, and:
a) grab an paste it in you report
b) EX this flow from your report
c) -INCLUDE this flow in your report

To modify an existing Oracle table you need to check your access file to be sure that the write permission is set to YES.