SQL Output contents of a table to string

Question

Hi,

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

Answer 1

You can try something like this

DECLARE @TABLE TABLE(
     SqlString VARCHAR(MAX)
)

INSERT INTO @TABLE (SqlString) SELECT 'SELECT 1'

DECLARE @SqlString VARCHAR(MAX)

SELECT  TOP 1 @SqlString = SqlString FROM @TABLE

EXEC (@SqlString)

Concatenate string from multiple rows

DECLARE @Table TABLE(
     ID INT,
     Val VARCHAR(50)
)
INSERT INTO @Table (ID,Val) SELECT 1, 'SELECT *'
INSERT INTO @Table (ID,Val) SELECT 2, 'FROM YourTable'
INSERT INTO @Table (ID,Val) SELECT 3, 'WHERE 1 = 1'

DECLARE @SqlString VARCHAR(MAX)
--Concat
SELECT  DISTINCT 
     @SqlString =
     (
      SELECT tIn.Val + ' '
      FROM @Table tIn
      ORDER BY ID
      FOR XML PATH('')
     )
FROM    @Table t

PRINT @SqlString

Answer 2

if you want to execute a string of sql then use Exec() or sp_executeSql

Answer 3

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.