Help On Query

  • DECLARE @listStr VARCHAR(MAX)

    SELECT @listStr = COALESCE(@listStr+''''+',' ,'') + ''''+Name

    FROM sys.all_columns where object_id = object_id ('TableName')

    SELECT 'insert into tablename values ('+@listStr+''''+')'

    Could any one please explain how the above query works?

  • This query concatenates all the values found in the name column of the sys.all_columns table for a specific table.

    The coalesce function is used to take into account that the very first element doesn't need to be preceded with a comma.

    In the first step, @listStr is declared as a string variable, but it is not initialized. Therefore it has the value NULL.

    You can consider the SELECT statement as some sort of loop, which goes over each row of the sys.all_columns table and takes the current name in each iteration and concatenates it to the string variable. For the first row, @listStr is still NULL, so COALESCE returns the empty string, giving the result @listStr = 'column1.

    For the second row, @listStr is not NULL anymore, so now you get the result @listStr = 'column1', 'column2.

    In each iteration, the current column is appended to the string in the following form: + ','columnX

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • I am not recommending that you use this but query, I am posting it for reference. It will produce the same result and it's easier to read & understand.

    DECLARE @listStr VARCHAR(MAX)='insert into tablename values (';

    SELECT @listStr = @listStr+''''+Name+''','

    FROM sys.all_columns WHERE object_id = object_id ('you table');

    SET @listStr=LEFT(@listStr,LEN(@listStr)-1)+')';

    SELECT @listStr;

    Alternatively, you can do it like this...

    SELECT @listStr='insert into tablename values ('+

    (SELECT ''''+Name+''''+','

    FROM sys.all_columns

    WHERE object_id = object_id ('reports.dbo.wt_rpt_peo_educ_lang')

    FOR XML PATH(''))+')';

    SELECT REPLACE(@listStr,',)',')')

    Koen did a great job explaining your query. I just wanted to include a couple alternatives that do the same thing and are easier to understand.

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • Alan.B (8/29/2013)


    Koen did a great job explaining your query. I just wanted to include a couple alternatives that do the same thing and are easier to understand.

    Sure, the XML PATH query is easier to understand 😉

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply