Loading data to temp table from staging table

Question

I have loaded CSV file into staging area. Now I want to shift this file to temporary table. For that I created 3 different schemas, ie STG ( for staging area ), TMP ( for temporary area ) and TGT ( for target area or datawarehouse). This is staging table on STG schema.

GRANT USAGE ON SCHEMA PRASHANT_DWH.STG TO ROLE ACCOUNTADMIN;
CREATE OR REPLACE TABLE PRASHANT_DWH_STG_COUNTRY (
    ID NUMBER(38,0) NOT NULL,
    COUNTRY_DESC VARCHAR(256)
    );

This is temp table on TMP schema. I have added few more constraints like maintenance columns and surrogate keys.

GRANT USAGE ON SCHEMA PRASHANT_DWH.TMP TO ROLE ACCOUNTADMIN;
CREATE OR REPLACE TABLE PRASHANT_DWH_TMP_COUNTRY (
    ID NUMBER(38,0) NOT NULL,
    COUNTRY_DESC VARCHAR(256),
    RCD_INS_TS TIMESTAMP, -- aaja ko date
    RCD_UPD_TS TIMESTAMP, -- aaja ko date, if updated then only updated date
    RCD_START_DI DATE, -- system date 
    RCD_CLOSE_DI DATE, -- future date  
    RCD_CLOSE_FLG VARCHAR(1),
    ID_SUR_KEY NUMBER NOT NULL, -- autogenerated, else key + 1, 
    PRIMARY KEY (ID_SUR_KEY) 
    );

And I tried to merge these two tables and insert maintenance column surrogate keys. but it kept showing errors

   MERGE TMP.PRASHANT_DWH_TMP_COUNTRY AS TC
   USING STG.PRASHANT_DWH_STG_COUNTRY AS SC
   ON PDTC.ID = PDSC.ID 
   WHEN MATCHED THEN 
        UPDATE PDTC.ID_SUR_KEY = PDSC.ID + 1 
        INSERT (ID, COUNTRY_DESC,RCD_INS_TS,RCD_UPD_TS,RCD_START_DI,RCD_CLOSE_DI,RCD_CLOSE_FLG,ID_SUR_KEY) 
        VALUES (SC.ID,SC.COUNTRY_DESC,current_timestamp,current_timestamp,current_date,current_date,N,SC.ID + 1)
   WHEN NOT MATCHEN BY TARGET THEN 
        DELETE

Gives the errors:

SQL compilation error: 
  syntax error line 1 at position 6 unexpected 'TMP'. 
  syntax error line 5 at position 15 unexpected 'PDTC'. 
  syntax error line 6 at position 8 unexpected 'INSERT'. 
  syntax error line 6 at position 32 unexpected ','. 
  syntax error line 6 at position 43 unexpected ','. 
  syntax error line 6 at position 54 unexpected ','. 
  syntax error line 6 at position 67 unexpected ','. 
  syntax error line 6 at position 80 unexpected ','. 
  syntax error line 6 at position 94 unexpected ','. 
  syntax error line 6 at position 105 unexpected ')'. 
  syntax error line 7 at position 37 unexpected ','. 
  syntax error line 7 at position 55 unexpected ','. 
  syntax error line 7 at position 73 unexpected ','. 
  syntax error line 7 at position 86 unexpected ','. 
  syntax error line 7 at position 99 unexpected ','. 
  syntax error line 7 at position 101 unexpected ','. 
  syntax error line 7 at position 108 unexpected '+'. 
  syntax error line 7 at position 111 unexpected ')'.

user_43992 · Answer

the final merge is missing a few things.

the INTO after MERGE
I also fully qualified the table name (maybe not needed)
you use the alias PDTC & PDSC it they are TC & SC
The N being inserted in RCD_CLOSE_FLG, is a char, so it needs to be wrapped in single quotes 'N'

MERGE INTO PRASHANT_DWH.TMP.PRASHANT_DWH_TMP_COUNTRY AS TC
USING PRASHANT_DWH.STG.PRASHANT_DWH_STG_COUNTRY AS SC
    ON TC.ID = SC.ID 
WHEN MATCHED THEN 
    UPDATE TC.ID_SUR_KEY = SC.ID + 1
    INSERT (ID, COUNTRY_DESC, RCD_INS_TS, RCD_UPD_TS, RCD_START_DI, RCD_CLOSE_DI, RCD_CLOSE_FLG, ID_SUR_KEY) 
    VALUES (SC.ID, SC.COUNTRY_DESC, current_timestamp, current_timestamp, current_date, current_date, 'N', SC.ID + 1)
WHEN NOT MATCHEN BY TARGET THEN DELETE

But the tail of your MERGE SQL is a bit of a mess, and I am not sure how to fix it, as what you are trying to do is not clear to me.

The INSERT should be bound to a WHEN MATCHED THEN or WHEN NOT MATCHED THEN and your WHEN NOT MATCHEN BY TARGET THEN DELETE seems..

It ether should be WHEN NOT MATCHEN THEN DELETE, but that seems to interfere with the INSERT.

It makes the most sense to UPDATE & Insert, and ignore the DELETE path,

MERGE INTO PRASHANT_DWH.TMP.PRASHANT_DWH_TMP_COUNTRY AS TC
USING PRASHANT_DWH.STG.PRASHANT_DWH_STG_COUNTRY AS SC
    ON TC.ID = SC.ID 
WHEN MATCHED THEN 
    UPDATE TC.ID_SUR_KEY = SC.ID + 1
WHEN NOT MATCHED THEN
    INSERT (ID, COUNTRY_DESC, RCD_INS_TS, RCD_UPD_TS, 
        RCD_START_DI, RCD_CLOSE_DI, RCD_CLOSE_FLG, ID_SUR_KEY) 
    VALUES (SC.ID, SC.COUNTRY_DESC, current_timestamp, 
        current_timestamp, current_date, current_date, 'N', 
        SC.ID + 1);

Loading data to temp table from staging table

1 Answers