Help On Query

Question

Help On Query

vignesh.ms

Hall of Fame

Points: 3456
More actions
August 29, 2013 at 6:14 am

#277445

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?

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

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

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 1

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

Alan Burstein SSC Guru Points: 61141 More actions · Answer 2

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

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 3

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