-4

Scenario:

Is source table SourceData (Name, Number, Date)

Is existing table ProdData (ProdName, ProdNumber, CreatedDate)

Requirement:

Dont import from source if already exists in prod data!!!

Import rows from source to prod data, keep existing rows, append new ones, map columns like:

  • Name -> ProdName
  • Number -> ProdNumber
  • Date -> CreatedDate (IF Date NULL add SystemDate time)

Output data example:

SourceData

Name | Number | Date
-----+--------+------ 
A    | 1      | 2012
B    | 2      | NULL

ProdData

ProdName | ProdNumber | CreatedDate
---------+------------+------------
Existing |    123     | 2018
A        |    1       | 2012    
B        |    2       | 2020
user3462947
  • 89
  • 10
  • SQL Server. Needs to be done in CTAS style – user3462947 Mar 27 '20 at 08:42
  • You specify CTAS, but I think you need `merge`: https://learn.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql?view=sql-server-ver15. It is hard to answer when you specify *how* to answer, but your *how* is not correct. – Gordon Linoff Mar 27 '20 at 11:21
  • I suggest you write some CTAS syntax and take a look - how do you think you can include extra columns in the source select? It seems pretty obvious to me – Nick.Mc Mar 27 '20 at 11:30
  • merge cant be used, it is old server. – user3462947 Mar 27 '20 at 12:07
  • 1
    Please edit the question with the CTAS syntax that you are using. If you haven't tried any CTAS syntax yet, you really shouldn't be asking a question – Nick.Mc Mar 28 '20 at 01:45
  • Where is your source table ? how do you plan to import it ? – Venkataraman R Apr 01 '20 at 06:39
  • @user3462947: what you desribe is an update/insert from `SourceData` into target table `ProdData`. So I don't understand why you specifically ask for a CTAS query - which stands for `CREATE TABLE ... AS SELECT ... FROM ...` (so that creates a *new* table). – GMB Apr 02 '20 at 10:32

1 Answers1

0

Would something like this fit your needs?

INSERT ProdData (ProdName, ProdNumber, CreatedDate)
SELECT 
    SourceData.name as "ProdName",
    SourceData.Number as "ProdNumber",
    NVL(SourceData.Date,to_date(sysdate,'YYYY') as "CreatedDate"
FROM 
    SourceData
WHERE 
    NOT EXISTS (SELECT
                    1 
                FROM 
                    ProdData PD2 
                WHERE 
                    PD2.ProdName = SourceData.name
                    and PD2.ProdNumber = SourceData.Number
                    and NVL(PD2.CreatedDate,to_date(sysdate,'YYYY') = nvl(SourceData.Date,to_date(sysdate,'YYYY');

Another alternate option may be something like:

CREATE TABLE ProdData_new
WITH
    (
      DISTRIBUTION = HASH(ProdName)
    , CLUSTERED INDEX (ProdNumber)
    )
AS
SELECT 
      pdold.ProdName AS ProdName
    , pdold.ProdNumber AS ProdNumber
    , NVL(pdold.CreatedDate,to_date(sysdate,'YYYY')) AS CreatedDate
FROM      
    ProdData AS pdold

UNION 

SELECT 
      sdold.name AS ProdName
    , sdold.Number AS ProdNumber
    , NVL(sdold.Date,to_date(sysdate,'YYYY') AS CreatedDate
FROM 
    SourceData AS sdold
;

--Optional Renaming of old tables
--RENAME OBJECT ProdData TO ProdData_old;
--RENAME OBJECT SourceData TO SourceData_old;
--RENAME OBJECT ProdData_new TO ProdData;
--DROP TABLE ProdData_old;
--DROP TABLE SourceData_old;

Or possibly something more like:

CREATE TABLE ProdData_new
    ( ProdName NVARCHAR NOT NULL 
    , ProdNumber INT NOT NULL
    , CreatedDate INT NOT NULL)
WITH
    ( DISTRIBUTION = HASH(ProdName)
    , CLUSTERED INDEX (ProdNumber) )
AS
SELECT 
      pdold.ProdName AS ProdName
    , pdold.ProdNumber AS ProdNumber
    , NVL(pdold.CreatedDate,to_date(sysdate,'YYYY')) AS CreatedDate
FROM      
    ProdData AS pdold

UNION 

SELECT 
      sdold.name AS ProdName
    , sdold.Number AS ProdNumber
    , NVL(sdold.Date,to_date(sysdate,'YYYY') AS CreatedDate
FROM 
    SourceData AS sdold
;

--Optional Renaming of old tables
--RENAME OBJECT ProdData TO ProdData_old;
--RENAME OBJECT SourceData TO SourceData_old;
--RENAME OBJECT ProdData_new TO ProdData;
--DROP TABLE ProdData_old;
--DROP TABLE SourceData_old;

This is all somewhat guess work without knowing what you have tried, your specific syntax and your DB_Schema_Table setup.

bab245
  • 11
  • 1