Generate SQl from columns in a table

M Kerr 21 Reputation points
2022-01-31T20:57:59.153+00:00

DECLARE @alenzi as varchar(max), @Columns as varchar(max)

SELECT @Columns= 'a.' + Name + ' = ISNULL(b.' + Name + ',a.'+Name +'),'
FROM tempdb.sys.columns
WHERE object_id = Object_id('tempdb..##Assumptions');

REMOVE LAST , in @Columns

SELECT @alenzi = 'UPDATE a SET ' + @Columns + ' FROM Assumptions a INNER JOIN ##Assumptions b ON a.id = b.id'

SELECT @alenzi as Finished

When i run the SELECT @Columns statement i only get the 1st column of the table, i want to generate a list of columns for the statement. I also need to remove the last comma in @Columns.

My current results for Finished is - UPDATE a SET a.ExcelUpdateDate = ISNULL(b.ExcelUpdateDate,a.ExcelUpdateDate), FROM Assumptions a INNER JOIN ##Assumptions b ON a.id = b.id

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,551 questions
{count} votes

Accepted answer
  1. Yitzhak Khabinsky 24,941 Reputation points
    2022-01-31T21:20:13.27+00:00

    Hi @M Kerr ,

    Please try the following solution.

    It will work starting from SQL Server 2017 onwards.

    SQL

    USE tempdb;  
    GO  
      
    DROP TABLE IF EXISTS dbo.Hist;  
      
    CREATE TABLE dbo.Hist  
    (  
         CNPJ_ID INT IDENTITY(1,1) PRIMARY KEY,  
         TP_FUNDO VARCHAR(20) NULL,  
         CNPJ_FUNDO NVARCHAR(max) NULL  
    );  
      
    DECLARE @tbl_name VARCHAR(128) = 'Hist'  
       , @schema_name VARCHAR(128) = 'dbo'  
       , @columnList VARCHAR(MAX)  
      
    -- SQL Server 2017 onwards  
    SET @columnList = (SELECT STRING_AGG(QUOTENAME(COLUMN_NAME), ', ')  
        FROM INFORMATION_SCHEMA.COLUMNS  
        WHERE TABLE_CATALOG = 'tempdb'  
            AND TABLE_NAME = @tbl_name  
            AND TABLE_SCHEMA = @schema_name  
    );  
      
    SELECT @columnList;  
    
    SET @columnList = (SELECT STRING_AGG('a.' + QUOTENAME(COLUMN_NAME) + ' = ISNULL(b.' + QUOTENAME(COLUMN_NAME) + ',a.'+QUOTENAME(COLUMN_NAME) +')', ', ')  
        FROM INFORMATION_SCHEMA.COLUMNS  
        WHERE TABLE_CATALOG = 'tempdb'  
            AND TABLE_NAME = @tbl_name  
            AND TABLE_SCHEMA = @schema_name  
    );  
      
    SELECT @columnList;  
    

    Output

    [CNPJ_ID], [TP_FUNDO], [CNPJ_FUNDO]  
    
    a.[CNPJ_ID] = ISNULL(b.[CNPJ_ID],a.[CNPJ_ID]), a.[TP_FUNDO] = ISNULL(b.[TP_FUNDO],a.[TP_FUNDO]), a.[CNPJ_FUNDO] = ISNULL(b.[CNPJ_FUNDO],a.[CNPJ_FUNDO])  
    
    0 comments No comments

0 additional answers

Sort by: Most helpful