Sql-server – SQL Server: Recursive dynamic SQL puzzle

dynamic-sqlsql server

I was looking at this post of trim all columns of a table. In the answer query script the column names gets concatenated without any looping operation. I simplified it to check its work flow as explained below.

Create a new database

Create a new table with three columns as in below script.(insert
values not required)

Create table table1(id int, name varchar(20), city varchar(20))

Execute below T-SQL Script, which results xxx [id] [name] [city] in single row.

Declare @a as varchar(4000)
SET @a = 'xxx '
SELECT @a = @a + '['+COLUMN_NAME+'] ' FROM INFORMATION_SCHEMA.COLUMNS 
SELECT @a

Where as below script results in 3 rows

Declare @a as varchar(4000)
SET @a = 'xxx '
SELECT 'b' = @a + '['+COLUMN_NAME+'] ' FROM INFORMATION_SCHEMA.COLUMNS

To understand more better I executed the below query using Top 1. This results xxx [id]

Declare @a as varchar(4000)
SET @a = 'xxx '
SELECT Top 1 @a = @a + '['+COLUMN_NAME+'] ' FROM INFORMATION_SCHEMA.COLUMNS 
SELECT @a

I got puzzled With Top 2 query. This resulted xxx [id] [name].

Where as I was expecting xxx [id] xxx [id] [name] i.e. First value + second value. Where am I going wrong.

I am curious to know how the column names getting concatenated. Sorry I could not frame better title for this puzzle.

Best Answer

I am curious to know how the column names getting concatenated.

Can't really answer that. Some internal workings of SQL Server creates a result like that, sometimes.

The technique has been around for a considerable amount of time. Microsoft does not support the functionality and advises not to use it.

From SET @local_variable (Transact-SQL)

Do not use a variable in a SELECT statement to concatenate values (that is, to compute aggregate values). Unexpected query results may occur. This is because all expressions in the SELECT list (including assignments) are not guaranteed to be executed exactly once for each output row.

Instead you can use a cursor or the for xml trick to do the same.

declare @a as varchar(4000);
set @a = 'xxx ';

set @a = @a + (
              select quotename(COLUMN_NAME) as '*'
              from INFORMATION_SCHEMA.COLUMNS
              for xml path(''), type
              ).value('text()[1]', 'varchar(4000)');

Result: xxx [city][id][name]

Related Solutions

Sql-server – Oracle GoldenGate add trandata errors

I found out what the problem is, it seems that GoldenGate doesn't work with SQL Express. The server I was connecting to is SQL Express, I'll need to use the Enterprise Edition.

Sql-server – way to generate table create script in TSQL

Back in 2007, I asked for an easy way to generate a CREATE TABLE script via T-SQL rather than using the UI or SMO. I was summarily rejected.

However, SQL Server 2012 makes this very easy. Let's pretend we have a table with the same schema across multiple databases, e.g. dbo.whatcha:

CREATE TABLE dbo.whatcha
(
  id INT IDENTITY(1,1), 
  x VARCHAR(MAX), 
  b DECIMAL(10,2), 
  y SYSNAME
);

The following script uses the new sys.dm_exec_describe_first_results_set dynamic management function to retrieve the proper data types for each of the columns (and ignoring the IDENTITY property). It builds the #tmp table you need, inserts from each of the databases in your list, and then selects from #tmp, all within a single dynamic SQL batch and without using a WHILE loop (that doesn't make it better, just simpler to look at and allows you to ignore Database_Ref_No entirely :-)).

SET NOCOUNT ON;

DECLARE @sql NVARCHAR(MAX), @cols NVARCHAR(MAX) = N'';

SELECT @cols += N',' + name + ' ' + system_type_name
  FROM sys.dm_exec_describe_first_result_set(N'SELECT * FROM dbo.whatcha', NULL, 1);

SET @cols = STUFF(@cols, 1, 1, N'');

SET @sql = N'CREATE TABLE #tmp(' + @cols + ');'

DECLARE @dbs TABLE(db SYSNAME);

INSERT @dbs VALUES(N'db1'),(N'db2');
  -- SELECT whatever FROM dbo.databases

SELECT @sql += N'
  INSERT #tmp SELECT ' + @cols + ' FROM ' + QUOTENAME(db) + '.dbo.tablename;'
  FROM @dbs;

SET @sql += N'
  SELECT ' + @cols + ' FROM #tmp;';

PRINT @sql;
-- EXEC sp_executesql @sql;

The resulting PRINT output:

CREATE TABLE #tmp(id int,x varchar(max),b decimal(10,2),y nvarchar(128));
  INSERT #tmp SELECT id,x,b,y FROM [db1].dbo.tablename;
  INSERT #tmp SELECT id,x,b,y FROM [db2].dbo.tablename;
  SELECT id,x,b,y FROM #tmp;

When you are confident it's doing what you expect, just uncomment the EXEC.

(This trusts you that the schema is the same; it does not validate that one or more of the tables has since been changed, and may fail as a result.)

Best Answer

Related Solutions

Sql-server – Oracle GoldenGate add trandata errors

Sql-server – way to generate table create script in TSQL

Related Question