1

I want to be able to grab all of the records in a table into a comma delimited list that I can then use to insert into a table on another database. Due to permission restrictions on the customer's server I cannot access any of the options when right-clicking on the database name, and all of the solutions I've found so far involve having permission to do so (e.g. Tasks > Export Data...)

I have tried using COALESCE to do this, however the problem is that my table could have any number of columns. Columns can be added/deleted at any time through the UI by the users and therefore I cannot hard code the columns in my select statement.

Here is what I have written so far, using a simple CTE statement where there are three columns (RowCode, RowOrd, RowText) and concatenating them into a variable that I print out. I just want to find a way to grab these column names dynamically instead of hard coding them. I'll also need to account for various types of column names by casting them each as varchar in the variable.

DECLARE @listStr VARCHAR(MAX)

;WITH tableData AS
(
    SELECT * 
    FROM tableRows
)
SELECT 
    @listStr = ISNULL(@listStr + 'select ','select ') + '''' + RowCode + ''',''' + cast(RowOrd as varchar) + ''',''' + RowText + '''' + Char(13) 
FROM 
    tableData

PRINT @listStr

The tableRows table contains the following records

RowCode RowOrd  RowText
-----------------------
RowA    1       Row A
RowB    2       Row B

And the variable @listStr is currently printing this, which is correct

select 'RowA','1.00','Row A'
select 'RowB','2.00','Row B'

Thanks in advance!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Ren222
  • 13
  • 3
  • What about `select *`? – Josh Part Jan 23 '17 at 18:18
  • Also, [related](http://stackoverflow.com/questions/600446/how-do-you-return-the-column-names-from-a-table) – Josh Part Jan 23 '17 at 18:43
  • If `Columns can be added/deleted at any time through the UI by the users `, then you need to see what you are doing. Get the permission you need or tell the people paying you that you are being denied the tools to do the job. – Dan Bracuk Jan 23 '17 at 19:04

1 Answers1

2

With a bit of XML you can dynamically gather and "stringify" your values

Declare @tableRows  table (RowCode varchar(50), RowOrd  int, RowText varchar(50))
Insert Into @tableRows  values
('RowA',1,'Row A'),
('RowB',2,'Row B')

Declare @listStr VARCHAR(MAX) = ''

Select @listStr = @listStr + C.String + char(13)
 From  @tableRows A
 Cross Apply (Select XMLData = cast((Select A.* for XML RAW) as xml)) B
 Cross Apply (
              Select String = 'select '+Stuff((Select ',' +Value 
               From  (
                        Select Value  = ''''+attr.value('.','varchar(max)')+''''
                         From  B.XMLData.nodes('/row') as A(r)
                         Cross Apply A.r.nodes('./@*') AS B(attr)
                     ) X
               For XML Path ('')),1,1,'') 
             ) C

Select @listStr

Returns

select 'RowA','1','Row A'
select 'RowB','2','Row B'
John Cappelletti
  • 79,615
  • 7
  • 44
  • 66
  • Thank you, this is exactly what I was looking for! I like the use of XML, hadn't thought of that. – Ren222 Jan 24 '17 at 05:14
  • P.S. is there a way to handle NULL values in this context? Some of the records contain NULL values and they are not showing up in the output. I've tried the following in the innermost select statement, but it doesn't seem to do the trick: Select Value = 'ISNULL('''+attr.value('.','varchar(max)')+''','''')' From B.XMLData.nodes('/row') as A(r) Cross Apply A.r.nodes('./@*') AS B(attr) – Ren222 Jan 24 '17 at 06:12
  • @Ren222 Sorry, I forgot to mention that NULL would be excluded. I'll noodle it a bit more to see if I can come up with a work-around – John Cappelletti Jan 24 '17 at 10:00
  • Very nice John! – ASH Jan 28 '17 at 19:11
  • @ryguy72 Thanks for the grin :) – John Cappelletti Jan 28 '17 at 19:12
  • Maybe this isn't the right place to ask, but how the heck do you get points on SO? I have posted about 50 solutions here, all 100% correct, and I have almost no points. Doesn't make sense. – ASH Jan 28 '17 at 19:59
  • @ryguy72 Take a peek at http://meta.stackexchange.com/questions/17204/six-simple-tips-to-get-reputation-fast-on-any-stack-exchange-site but there is no silver-bullet. – John Cappelletti Jan 28 '17 at 20:12