SQL Output contents of a table to string

Question

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

score 9 · Accepted Answer · answered Dec 17 '09 at 13:08

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.

thats goodm, thanks. The only problem I do have is that my table contains more statements than varchar(max) can handle. Whats another suitable datatype I can use. I am using SQL Server 2008 if this helps. — Belliez, Dec 17 '09 at 14:14

Adriaan Stander · Answer 2 · 2009-12-17T15:00:16.790

2

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

edited Dec 17 '09 at 15:00

answered Dec 17 '09 at 13:07

Adriaan Stander

162,879
31
289
284

the table I am converting into a string is larger than VARCHAR(MAX)? Can you recommend a suitable datatype. The table contains around 400 rows. Thanks, this is almost what I am looking for. – Belliez Dec 17 '09 at 14:15
Have a look here teratrax.com/articles/varchar_max.html. It states *These data types can hold the same amount of data BLOBs can hold (2 GB)* – Adriaan Stander Dec 17 '09 at 14:35
interesting, my sql query definitely does not pass 2GB!! I am using VARCHAR(MAX) as per your declare above. – Belliez Dec 17 '09 at 14:38
Are you looking to concatenate the sql strings from the table before you execute it, or execute all of the entries one at a time? What seems to be the issue? – Adriaan Stander Dec 17 '09 at 14:41
The table contains about 300 rows each containing a line from a single SQL query (I edited my question to reflect this now, hope this makes it clearer). Using a combination of your answer and the answer from concernedoftunbridgewells above I attempt to "EXEC @sqlString" but I get an error so I looked at the string using "print @sqlstring" and I can only see the first 16 rows from the table which is why I am getting the error. This is why I thought the @SqlString was maxed out! Obviously not – Belliez Dec 17 '09 at 14:56
Well then you would want to concatenate the string before you exec it. – Adriaan Stander Dec 17 '09 at 14:57
I found the issue. I used EXEC @sqlstring instead of EXEC (@sqlstring). This made all the difference. – Belliez Dec 17 '09 at 15:02
I thank you for your help as it pointed me in the right direction. Not sure why this would make the difference though. – Belliez Dec 17 '09 at 15:04

score 0 · Answer 3 · answered Dec 17 '09 at 13:08

0

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

answered Dec 17 '09 at 13:08

Mladen Prajdic

15,457
2
43
51

SQL Output contents of a table to string

3 Answers3