asked
How To Create a Dynamic UNION Statement
Hi there, I am trying to create a dynamic UNION statement of all tables containing the string 'TEST'. Some of the columns have differing number of columns and **only all columns are required if the table is its most recent**. For example, say I have 3 TEST tables, one for each of the past few years: CREATE TABLE TEST_2015( ID INT, Control1 VARCHAR(1), Control2 VARCHAR(1)) INSERT INTO TEST_2015 VALUES(1,'A','A') INSERT INTO TEST_2015 VALUES(1,'B','B') CREATE TABLE TEST_2016( ID INT, Control1 VARCHAR(1), Control2 VARCHAR(1), Control3 VARCHAR(1)) INSERT INTO TEST_2016 VALUES(2,'C','C','C') INSERT INTO TEST_2016 VALUES(2,'D','D','D') INSERT INTO TEST_2016 VALUES(2,'E','E','E') CREATE TABLE TEST_2017( ID INT, Control1 VARCHAR(1), Control3 VARCHAR(1)) INSERT INTO TEST_2017 VALUES(3,'F','F') I need to be able to dynamically create the below UNION statement with the user only searching for the table name TEST. Also as the most recent table TEST_2017 only contains columns Control1 and Control 3, the query created should exclude Control2 column, and if Control3 column doesn't exist in any of the tables then a 'NULL AS Control3' needs to be included. So in this example the dynamic UNION statement should look like this: SELECT ID, Control1, Control3 FROM TEST_2017 UNION SELECT ID, Control1, Control3 FROM TEST_2016 UNION SELECT ID, Control1, NULL AS Control3 FROM TEST_2015 Note: Not all columns are labelled Control. It's just for illustrative purposes in this example. TIA [EDIT] This SQL brings the required columns from the most recent table created called TEST: SELECT TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME LIKE 'TEST_2%' AND COLUMN_NAME IN (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = (SELECT [name] FROM sys.tables WHERE [name] LIKE 'TEST_2%' AND create_date = (SELECT MAX(create_date) FROM sys.tables WHERE [name] LIKE 'TEST_2%'))) ORDER BY TABLE_NAME DESC, COLUMN_NAME ASC
@David Do you need any way to identify where the rows are coming from in the final result of that dynamic union? Without it the **UNION** will collapse the rows from multiple tables if they have same values in all columns unless **UNION ALL** is used. Please let me know. Even if the data excludes such possibility (perhaps because IDs are guaranteed to be different), still **UNION ALL** is lighter than **UNION** because it does not attempt to aggregate the resulting set but simply renders it out as is.
@Oleg Thanks for the reply. No I do not need to know which table the rows are coming from and there should be no duplicates between tables. I just need to be able to generate the dynamic UNION or UNION ALL statement. As I am not expecting duplicates UNION ALL should be grand.
The solution below uses UNION ALL in place of the UNION. It is based on the select statement which returns one row per table featuring TEST\_2 as a part of its name. The results are sorted so that the latest table comes to the top. Then the cross apply is used for each table to figure that matching columns' list substituting the missing column with null added to the left of it. To collapse the rows from cross apply into one row for each table, the for xml path is used. Finally, the same for xml path is applied to the whole result in order to generate the dynamic script. Here is the solution: -- The object_id of the last table which features TEST_2 as a part of its name. -- This table dictates the column list of the final select statement declare @lastTableObjectID int; -- nvarchar variable to store the final select statement which can be then executed declare @sql nvarchar(max); select top 1 @lastTableObjectID = [object_id] from sys.tables where name like 'test|_2%' escape '|' order by create_date desc -- generate dynamic select statement select @sql = stuff( ( select 'union all select ' + List + ' from ' + TableName + char(10) from ( select top 1000 /* cheesy, but is needed to ensure desc table sort */ t.[name] TableName, stuff(c.List, 1, 2, '') List from sys.tables t cross apply ( select ', ' + isnull(b.[name], 'null ' + a.[name]) from sys.columns a left join sys.columns b on a.[name] = b.[name] and b.[object_id] = t.[object_id] where a.[object_id] = @lastTableObjectID for xml path('') ) c(List) where name like 'test|_2%' escape '|' order by create_date desc ) final for xml path('')), 1, 10, '' ); -- uncomment next line to see how the resulting script looks like --print @sql -- execute dynamic script to see results exec sp_executesql @statement = @sql; go To test the solution, I created 4 tables (2014 through 2017). 2014 had columns ColA, ColB, ColC; 2015 - ColA through ColD; 2016 - ColA, ColB, ColD, and 2017 - ColA, ColB, ColD, ColE. The resulting dynamic script looked like this: select ID, ColA, ColB, ColD, ColE from Test_2017 union all select ID, ColA, ColB, ColD, null ColE from Test_2016 union all select ID, ColA, ColB, ColD, null ColE from Test_2015 union all select ID, ColA, ColB, null ColD, null ColE from Test_2014 Hope this helps. Oleg
18 People are following this question.
