SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Help On Query


Help On Query

Author
Message
vignesh.ms
vignesh.ms
Old Hand
Old Hand (368 reputation)Old Hand (368 reputation)Old Hand (368 reputation)Old Hand (368 reputation)Old Hand (368 reputation)Old Hand (368 reputation)Old Hand (368 reputation)Old Hand (368 reputation)

Group: General Forum Members
Points: 368 Visits: 516
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?
Koen Verbeeck
Koen Verbeeck
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27699 Visits: 13268
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


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Alan.B
Alan.B
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5244 Visits: 7735
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.

-- Alan Burstein



Best practices for getting help on SQLServerCentral
Need to split a string? Try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL 2012+)
Need a pattern-based splitter? Try PatternSplitCM
Need to remove or replace those unwanted characters? Try PatExclude8K and PatReplace8K.

"I can't 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. " -- Itzek Ben-Gan 2001
Koen Verbeeck
Koen Verbeeck
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27699 Visits: 13268
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 ;-)


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search