Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Help On Query


Help On Query

Author
Message
vignesh.ms
vignesh.ms
SSC Veteran
SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)

Group: General Forum Members
Points: 200 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
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16546 Visits: 13210
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?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
Alan.B
Alan.B
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2246 Visits: 7426
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
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16546 Visits: 13210
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?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
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