0
SET @COLUMNNAMES_HIST= ( SELECT STRING_AGG( COLUMN_NAME, ' ,') COLUMN_NAMES FROM INFORMATION_SCHEMA.COLUMNS WHERE UPPER(TABLE_SCHEMA) = '@SCHEMANAME' AND UPPER(TABLE_NAME) = '@TABLE_HIST';                

SET @MERGESQL = 'MERGE ' +  @TABLE_HIST + ' USING #FINAL_TEMP_CPY SRC
ON (TGT.@PRIMARYKEY = SRC.@PRIMARYKEY AND TGT.UPDATE_TIMESTAMP = SRC.UPDATE_TIMESTAMP)
WHEN NOT MATCHED BY TARGET
            THEN INSERT
            (
                @COLUMNNAMES_HIST -- Column list
            ) 
        
            (
                SELECT *, GETTIMESTAMP(),  GETTIMESTAMP() FROM FINAL_TEMP_CPY;
            )'

I am fetching all the columns from TABLE_HIST first into a variable and that I am using in INSERT statement. Is that alright? And then in values I am fetching all values from source table and getting timestamp at the same time for other two columns in the TABLE_HIST. Do you think this will work?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
sga
  • 1
  • 2
  • you can find very useful information in folow links: https://community.powerbi.com/t5/Desktop/Dynamic-Query-Parameters-with-SQL-source/td-p/1740731 and http://www.a00b.com/Nw/DynamicStoredProcedureSQL.htm and https://learn.microsoft.com/en-us/answers/questions/406111/sql-dynamic-query-from-multiple-tables.html – Ali NajafZadeh Aug 03 '21 at 15:40
  • 2
    _Do you think this will work_ Why does anyone need to think at all? Jus try it and verify the result. And why do you need the complication of a MERGE statement when you only insert rows? INSERT will be simpler to write dynamically as well as easier to debug when an error occurs. And I can tell you now that your dynamic query will fail. Try it yourself with a static MERGE (or INSERT) statement. – SMor Aug 03 '21 at 17:07
  • I have rewritten following statement (SELECT *, IMS.GETTIMESTAMP(), IMS.GETTIMESTAMP() FROM FINAL_TEMP_CPY). Also I'm just writing the query, don't have access to the compiler that is the reason why i came here to ask – sga Aug 10 '21 at 06:48

0 Answers0