Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Help On Query Expand / Collapse
Author
Message
Posted Thursday, August 29, 2013 6:14 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, October 13, 2014 4:33 AM
Points: 141, Visits: 403

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?
Post #1489671
Posted Thursday, August 29, 2013 7:11 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:00 AM
Points: 13,250, Visits: 11,026
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 LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1489700
Posted Thursday, August 29, 2013 1:05 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, October 17, 2014 7:06 AM
Points: 612, Visits: 2,853
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



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)

"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

My blog
Post #1489853
Posted Thursday, August 29, 2013 1:19 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:00 AM
Points: 13,250, Visits: 11,026
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 LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1489871
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse