TSQLIncrementalLoadQlikScript

* ============================================================================
  Purpose:
  Generate Qlik incremental load script blocks for every base table
  in the current SQL Server database.
  
  Output:
  One PRINT block per table, including:
  - vLastLoadTime handling
  - LOAD statement with all columns
  - WHERE filter on detected timestamp column
  - LoadLog update block
  ============================================================================ */
-- Variable to hold current table name while iterating
DECLARE @TABLE_NAME AS NVARCHAR(255);
-- Cursor used to loop through all base tables
DECLARE @GenQlikLoad AS CURSOR;
-- Build cursor dataset: all user/base tables (excludes views)
SET @GenQlikLoad = CURSOR FOR
SELECT
   table_name
FROM INFORMATION_SCHEMA.TABLES
WHERE table_type = 'BASE TABLE';
-- Open cursor and fetch first table
OPEN @GenQlikLoad;
FETCH NEXT FROM @GenQlikLoad INTO @TABLE_NAME;
-- Loop through each table returned by cursor
WHILE @@fetch_status = 0
BEGIN
   BEGIN TRY
       -- Suppress "(X rows affected)" messages for cleaner output
       SET NOCOUNT ON;
       
       -- Holds comma-separated/formatted column list for Qlik LOAD
       DECLARE @Columns NVARCHAR(MAX);
       -- Holds PK column list (currently not used in generated output)
       DECLARE @KeyColumns NVARCHAR(MAX);
       -- Holds detected timestamp/last-modified column used in incremental WHERE
       DECLARE @TimestampColumn NVARCHAR(255);
       
       /* --------------------------------------------------------------------
          Build column list in ordinal order so generated LOAD matches table
          schema order. First column is formatted without leading comma.
          -------------------------------------------------------------------- */
       SELECT @Columns = STRING_AGG(
           CASE
               WHEN ORDINAL_POSITION = 1 THEN '     [' + column_name + ']'
               ELSE '    ,[' + column_name + ']'
           END,
           CHAR(13) + CHAR(10)
       ) WITHIN GROUP (ORDER BY ORDINAL_POSITION)
       FROM INFORMATION_SCHEMA.COLUMNS
       WHERE table_name = @TABLE_NAME;
       /* --------------------------------------------------------------------
          Capture primary key columns for table metadata/reference.
          Note: value is computed but not currently injected into PRINT output.
          -------------------------------------------------------------------- */
       SELECT @KeyColumns = STRING_AGG('[' + c.column_name + ']', ',')
       FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
       JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE c 
           ON tc.constraint_name = c.constraint_name
       WHERE tc.table_name = @TABLE_NAME 
           AND tc.constraint_type = 'PRIMARY KEY';
       /* --------------------------------------------------------------------
          Detect likely incremental timestamp column based on naming pattern
          and supported datetime types.
          Adjust this logic if your environment uses different naming.
          -------------------------------------------------------------------- */
       SELECT @TimestampColumn = column_name
       FROM INFORMATION_SCHEMA.COLUMNS
       WHERE table_name = @TABLE_NAME
           AND (column_name LIKE '%timestamp%' 
                OR column_name LIKE '%modified%' 
                OR column_name LIKE '%updated%')
           AND data_type IN ('datetime', 'datetime2', 'timestamp');
       /* --------------------------------------------------------------------
          Print Qlik script block for this table:
          1) Read last load time from LoadLog
          2) LOAD table columns from QVD
          3) Filter rows newer than vLastLoadTime
          4) Append load audit row into LoadLog
          -------------------------------------------------------------------- */
       PRINT @TABLE_NAME + ':' + CHAR(13) + CHAR(10) +
             '// Store the last load timestamp' + CHAR(13) + CHAR(10) +
             'LET vLastLoadTime = Peek(''LastLoadTime'', -1, ''LoadLog'');' + CHAR(13) + CHAR(10) +
             'IF IsNull(vLastLoadTime) THEN' + CHAR(13) + CHAR(10) +
             '    LET vLastLoadTime = ''1900-01-01'';' + CHAR(13) + CHAR(10) +
             'END IF;' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) +
             
             '// Load new and updated records' + CHAR(13) + CHAR(10) +
             'LOAD' + CHAR(13) + CHAR(10) +
             @Columns + CHAR(13) + CHAR(10) +
             'FROM ' + db_name() + '.' + SCHEMA_NAME() + '.' + @TABLE_NAME + '.qvd (qvd)' + CHAR(13) + CHAR(10) +
             'WHERE [' + @TimestampColumn + '] > ''$(vLastLoadTime)'';' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) +
             
             '// Update the load log' + CHAR(13) + CHAR(10) +
             'LoadLog:' + CHAR(13) + CHAR(10) +
             'LOAD' + CHAR(13) + CHAR(10) +
             '    ''$(vLastLoadTime)'' as PreviousLoadTime,' + CHAR(13) + CHAR(10) +
             '    Now() as LastLoadTime,' + CHAR(13) + CHAR(10) +
             '    ''' + @TABLE_NAME + ''' as TableName' + CHAR(13) + CHAR(10) +
             'AUTOGENERATE 1;' + CHAR(13) + CHAR(10) +
             '/////////////////////////////////////////////////////////////////////////////////////////////////';
   END TRY
   BEGIN CATCH
       -- Continue processing remaining tables if one table fails
       PRINT 'Error processing table ' + @TABLE_NAME + ': ' + ERROR_MESSAGE();
   END CATCH
   -- Fetch next table and continue loop
   FETCH NEXT FROM @GenQlikLoad INTO @TABLE_NAME;
END
-- Cleanup cursor resources
CLOSE @GenQlikLoad;
DEALLOCATE @GenQlikLoad;