Convert Oracle Database to SQL Server with Cascade Updates.

5 pts.
Tags:
Oracle
SQL Server
I am trying to convert Oracle database to SQL Server. Ten years ago I converted this same database from Access to Oracle. The database had a lot of cascade updates in it and some are not always downward flow. Sometimes they go up. I used after update triggers with deferred foreign key constraints. Worked good for ten years. Now they want this database in SQL Server. There are no deferred constraints in SQL Server. What is the best way to convert it? Right now I am using Instead of Update Triggers. Is this the right approach?

Software/Hardware used:
SQL Server 2012

Answer Wiki

Thanks. We'll let you know when a new response is added.

CREATE OR REPLACE PROCEDURE SP_TXN

AUTHID CURRENT_USER AS

CURSOR C IS SELECT * FROM TXN;

TR TXN%ROWTYPE;

H_ID VARCHAR2(10);

L_DT DATE:=TO_DATE(SYSDATE);

DT
VARCHAR2(30):=’TXN_’||TO_CHAR(SYSDATE,’DD_MON_YYYY_HH24_MI_SS’);

CNT NUMBER;

CNT1 NUMBER;

CNT2 NUMBER;

TNM VARCHAR2(40);

TT VARCHAR2(100);

BEGIN

SELECT ‘H’||S1.NEXTVAL INTO H_ID

FROM DUAL;

SELECT COUNT(*) INTO CNT

FROM USER_OBJECTS

WHERE OBJECT_TYPE=’TABLE’

AND OBJECT_NAME LIKE ‘TXN%’ AND
OBJECT_NAME<>’TXN_HIST’

AND TO_CHAR(CREATED,’dd-mon-yy’)=TO_CHAR(SYSDATE,’dd-mon-yy’);

SELECT COUNT(*) INTO CNT1

FROM USER_TABLES

WHERE TABLE_NAME=’TXN_HIST’;

IF CNT=0 THEN

EXECUTE IMMEDIATE ‘CREATE TABLE ‘||DT||’ AS SELECT * FROM
SCOTT.TXN’;

  IF CNT1=0 THEN

    EXECUTE IMMEDIATE
‘CREATE TABLE TXN_HIST AS SELECT * FROM SCOTT.TXN’;

    EXECUTE IMMEDIATE
‘ALTER TABLE TXN_HIST ADD HIST_ID VARCHAR2(20) ADD LOADED_DT DATE’;

    EXECUTE IMMEDIATE
‘UPDATE TXN_HIST SET HIST_ID=:1,LOADED_DT=:2’ USING H_ID,L_DT;

  END IF;

ELSE

DBMS_OUTPUT.PUT_LINE(‘TODAYS TABLE ALREADY CREATED’);

END IF;

SELECT OBJECT_NAME INTO TNM

FROM USER_OBJECTS

WHERE OBJECT_TYPE=’TABLE’

AND OBJECT_NAME LIKE ‘TXN%’ AND
OBJECT_NAME<>’TXN_HIST’

AND
TO_CHAR(CREATED,’dd-mon-yy’)=TO_CHAR(SYSDATE,’dd-mon-yy’); 

OPEN C;

LOOP

FETCH C INTO TR;

EXECUTE IMMEDIATE ‘SELECT COUNT(*) FROM TXN_HIST WHERE
TXN_ID=’||TR.TXN_ID INTO CNT2;

IF CNT2=0 THEN

EXECUTE IMMEDIATE ‘INSERT INTO TXN_HIST
VALUES(:1,:2,:3,:4,:5,:6,:7,:8)’ USING
TR.TXN_ID,TR.CUST_ID,TR.PROD_ID,TR.QTY,TR.TXN_DT_TM,TR.AMT,H_ID,L_DT;

EXECUTE IMMEDIATE ‘INSERT INTO ‘||TNM||’
VALUES(:1,:2,:3,:4,:5,:6)’ USING
TR.TXN_ID,TR.CUST_ID,TR.PROD_ID,TR.QTY,TR.TXN_DT_TM,TR.AMT;

END IF;

EXIT WHEN C%NOTFOUND;

END LOOP;

END;

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • danieljones
    Check out this article https://msdn.microsoft.com/en-us/library/hh313200(v=sql.110).aspx. I hope it will helpful for you.
    2,810 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: