Using loop with UNION t-sql

问题:

I have a need to query several tables, with similar data, but I don't want to write a lengthy UNION statement. Also, the number of tables can vary. Currently I accomplish this using Coldfusion by:

1. looping over a list of tablenames
2. performing the UNION inside the loop

Like this

set i = 0
set table_suffixes = "blue,green,red,yellow"
loop list="table_suffixes" index="idx_suffix"
   set tName = "table_" & idx_suffix
   if i > 0
     UNION
   end if
   select *
   FROM tName
end loop

Can this be done in T-SQL? I have not been able to find any articles about using a list to control a loop. The closest I could find would be to place a query in the loop. Like This

While (SELECT table_suffix FROM vendor_name WHERE discipline = 'electrical')
BEGIN 
  *union query...*
END

Any help to point me in the right direction is much appreciated. I am using SQL-Server 2008

回答1:

Although generally nor recommended, here is an example of generating and executing dynamic SQL within T-SQL.

Note I have used UNION ALL. You should understand the differences between this and UNION

Once you have this working you could wrap it in a a stored procedure that accepts a parameter to use for DISCIPLINE

Also please note, the fact you have to do this implies that you have some serious design flaws in your database.

DECLARE @TableName VARCHAR(100)
DECLARE @DSQL VARCHAR(4000)
SET @DSQL = ''

DECLARE cTableList CURSOR FOR
SELECT table_suffix FROM vendor_name WHERE discipline = 'electrical'

OPEN cTableList

FETCH NEXT FROM cTableList INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @DSQL = @DSQL + 'SELECT * FROM ' + @TableName + ' UNION ALL '
END


CLOSE cTableList
DEALLOCATE cTableList

-- Remove the last UNION ALL
IF LEN(@DSQL) > 11 SET @DSQL = LEFT(@DSQL,LEN(@DSQL) - 11)

-- Print it out for debugging purposes
PRINT (@DSQL)

-- Execute it
EXEC (@DSQL)

回答2:

Try this one -

Query:

DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = STUFF((
    SELECT CHAR(13) + 'UNION ALL' + CHAR(13) + 'SELECT * FROM [' + s.name + '].[' + o.name + ']'
    FROM sys.objects o 
    JOIN sys.schemas s ON o.[schema_id] = s.[schema_id]
    WHERE o.[type] = 'U'
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 11, '')

PRINT @SQL

Output:

SELECT * FROM [dbo].[test1]
UNION ALL
SELECT * FROM [dbo].[MyTable]
UNION ALL
...

In your case see something like this -

DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = STUFF((
    SELECT CHAR(13) + 'UNION ALL' + CHAR(13) + 'SELECT * FROM ' + table_suffix
    FROM vendor_name 
    WHERE discipline = 'electrical'
    WHERE o.[type] = 'U'
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 11, '')

PRINT @SQL