Dynamic merge into statement

Database
Enthusiast

Dynamic merge into statement

Is there a way to create dynamic merge into statement  through a script for all the tables in a table?

 

I usually create insert into statement with below sql.But Iam not sure how to generate the update statement


SELECT
   'INSERT INTO dbname.RowCounts ' ||
   'SELECT ' ||
   '''' || DatabaseName || '''' || ',' ||
   '''' || TableName || '''' || ',' ||
   'CAST(COUNT(*) AS BIGINT)' ||  
   'FROM ' || DatabaseName ||
   '.' || TableName || ';'
FROM dbc.tablesV
WHERE tablekind='T'

2 REPLIES
Teradata Employee

Re: Dynamic merge into statement

A look at the examples in the SQL DML manual should give you a good idea.  Something like this:

 

SELECT
   'Merge INTO dbname.RowCounts ' ||
   'Using (SELECT ' ||
   '''' || DatabaseName || '''' || ',' ||
   '''' || TableName || '''' || ',' ||
   'CAST(COUNT(*) AS BIGINT)' ||
   'FROM ' || DatabaseName ||
   '.' || TableName || ') As R (DName, TName, RCnt)' ||
   'On <Col1> = DName and <Col2> = TName' ||
   'When Matched then Update Set <Col3> = RCnt' ||
   'When Not Matched then Insert(DName, TName, RCnt) ;'
FROM dbc.tablesV
WHERE tablekind='T'

Re: Dynamic merge into statement

Hello,

 

I created a script that looks like this. Hope it helps.It works for tables with a single column as PI, but can be easily adapted for multiple use cases.

 

SyntaxEditor Code Snippet

SELECT    
   'MERGE INTO '|| a.DatabaseName || '.' || a.TableName ||' AS TARGET ' ||
   'USING ' || a.DatabaseName|| '.'|| a.TableName|| ' AS SOURCE ' ||
   'ON TARGET.' || b.ColumnName|| '= SOURCE.' ||b.ColumnName||
   ' WHEN MATCHED THEN UPDATE ' ||
   ' SET '|| c.ColumnNames ||
   ' WHEN NOT MATCHED THEN INSERT VALUES('|| d.SourceColumnNames  || ');'
   AS MERGE_STATEMENT
FROM            DBC.tablesV a
INNER JOIN DBC.IndicesV b  ON a.DatabaseName = b.DatabaseName AND a.TableName = b.TableName
INNER JOIN 
           (SELECT  
                        a.DatabaseName,a.TableName,TRIM(TRAILING ',' FROM(XMLAGG(a.ColumnName || '= SOURCE.'|| a.ColumnName || ',' ORDER BY a.ColumnID ) (VARCHAR(10000)))) as ColumnNames
            FROM dbc.columnsV a
                    LEFT JOIN DBC.IndicesV b  
                                        ON a.DatabaseName = b.DatabaseName AND a.TableName = b.TableName AND a.ColumnName = b.ColumnName
            WHERE b.IndexName is null
            GROUP BY a.DatabaseName,a.TableName            ) c     
                ON a.DatabaseName = c.DatabaseName and a.TableName = c.TableName
INNER JOIN           (SELECT  
                           a.DatabaseName,a.TableName,TRIM(TRAILING ',' FROM(XMLAGG('SOURCE.'|| a.ColumnName || ',' ORDER BY a.ColumnID ) (VARCHAR(10000)))) as SourceColumnNames
            FROM dbc.columnsV a
            GROUP BY a.DatabaseName,a.TableName            ) d
                ON a.DatabaseName = d.DatabaseName AND a.TableName = d.TableName
    WHERE a.tablekind='T' 
            AND a.DatabaseName = 'DEV_EDW' AND a.TableName = 'DIM_XXXXXX';