Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Output contents of a table to string

I have a table that contains many rows of SQL commands that make up a single SQL statement (to which I am grateful for this answer, step 5 here)

I have followed the example in this answer and now have a table of SQL - each row is a line of SQL that build a query. I can copy and paste the contents of this table into a new query window and get the results however due to my lack of SQL knowledge I am not sure how I go about copying the contents of the table into a string variable which I can then execute.

Edit: The SQL statement in my table comprises of 1 row per each line of the statement i.e.

Row1: SELECT * FROM myTable
Row2: WHERE
Row3: col = @value

This statement if copied into a VARCHAR(MAX) exceeds the MAX limit.

I look forward to your replies. in the mean time I will try myself.

Thank you

like image 463
Belliez Avatar asked Dec 17 '09 13:12

Belliez


People also ask

How retrieve entire contents of a table in SQL?

SELECT statements SELECT column1, column2 FROM table1, table2 WHERE column2='value'; In the above SQL statement: The SELECT clause specifies one or more columns to be retrieved; to specify multiple columns, use a comma and a space between column names. To retrieve all columns, use the wild card * (an asterisk).

How do you display text in SQL query?

:Explanation: Note: You can use literal string (enclosed in single or double quotation mark) just like we use a column name in the SELECT statement. If you use the literal string with a column then it will be displayed in every row of the query results.


1 Answers

You can use coalesce to concatenate the contents of a column into a string, e.g.

create table foo (sql varchar (max));

insert foo (sql) values ('select name from sys.objects')
insert foo (sql) values ('select name from sys.indexes')

declare @sql_output varchar (max)
set @sql_output = ''       -- NULL + '' = NULL, so we need to have a seed
select @sql_output =       -- string to avoid losing the first line.
       coalesce (@sql_output + sql + char (10), '')
  from foo

print @sql_output

Note: untested, just off the top of my head, but a working example of this should produce the following output:

select name from sys.objects
select name from sys.indexes

You can then execute the contents of the string with exec (@sql_output) or sp_executesql.

like image 79
ConcernedOfTunbridgeWells Avatar answered Nov 12 '22 04:11

ConcernedOfTunbridgeWells