Geek Answers Handbook

SQL Output table contents to string

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

I followed the example in this answer and now have an SQL table - each row is an SQL row that builds the 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 knowledge of SQL. I'm not sure how I'm going to copy the contents of the table into a string variable that I can execute.

Edit: The SQL statement in my table consists of 1 row per row of the ie statement

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

This statement, if copied to VARCHAR (MAX), exceeds the MAX limit.

I look forward to your answers. at the same time I will try myself.

thanks

+6
sql-server tsql
source share
3 answers

You can use coalesce to concatenate the contents of a column into a row, for example.

 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, right from 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 using exec (@sql_output) or sp_executesql .

+9
source share

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)

Merge a string of multiple lines

 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
+1
source share

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

0
source share

More articles:


All Articles