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'

1 REPLY
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'