CODEWITHSUNDEEP
sql-server
Liz
Liz

Reputation: 1

How to create a SQL procedure to union tables from a growing list of tables In SQLserver

I need to consolidate information from multiple tables into a single table. The issue is new tables are being created and I have no control over when they are created or what they are called other than the beginning part of the name.

I would love it if this worked:

Select t.name
into #aReading
from sys.tables as t
where t.name like 'Fast_a%'

Select *
from (Select name from #aReading)

This works but constantly needs new tables added

Insert (col1, col2) into TestTable

Select x.* from (col1,col2
from test1

union

Select col1,col2
from test1_01022019

union
Select col1,col2
from test1_09122019 ) x

I am still learning SQL and not very good with While but thought that might be the answer.

Upvotes: 0

Views: 872

Answers (1)

Max Szczurek
Max Szczurek

Reputation: 4334

Here's a dynamic SQL example (e.g. build the query as a string and then use built in procedure sp_executesql to execute the query).

This example uses STUFF and FOR XML PATH('') to concatenate a bunch of queries together and stick ' UNION ' between each one. 

DECLARE @sql NVARCHAR(MAX) = 'INSERT INTO TestTable (col1, col2) '

SELECT @sql += STUFF(
    (SELECT ' UNION SELECT col1, col2 FROM ' + t.name
    FROM sys.tables as t
    WHERE t.name like 'Fast_a%'
    FOR XML PATH('')), 1, 7, '')

EXEC sp_executesql @sql

So, for this schema:

CREATE TABLE fast_a1 (col1 INT, col2 INT)
CREATE TABLE fast_a2 (col1 INT, col2 INT)
CREATE TABLE fast_aasdf (col1 INT, col2 INT)
CREATE TABLE TestTable (col1 INT, col2 INT)

It builds this dynamic query:

INSERT INTO TestTable (col1, col2) 
SELECT col1, col2 FROM fast_a1 
UNION 
SELECT col1, col2 FROM fast_a2 
UNION 
SELECT col1, col2 FROM fast_aasdf

Edit:

Liz, run these statements (or here's a SQL fiddle) in sequence, and you'll see that they do insert data:

CREATE TABLE fast_a1 (col1 INT, col2 INT);
CREATE TABLE fast_a2 (col1 INT, col2 INT);
CREATE TABLE fast_aasdf (col1 INT, col2 INT);
CREATE TABLE TestTable (col1 INT, col2 INT);

INSERT INTO dbo.fast_a1 VALUES (1, 1);
INSERT INTO dbo.fast_a2 VALUES (2, 2);
INSERT INTO dbo.fast_aasdf VALUES (3, 3);

DECLARE @sql NVARCHAR(MAX) = 'INSERT INTO TestTable (col1, col2) '

SELECT @sql += STUFF(
    (SELECT ' UNION SELECT col1, col2 FROM ' + t.name
    FROM sys.tables as t
    WHERE t.name like 'Fast_a%'
    FOR XML PATH('')), 1, 7, '')

EXEC sp_executesql @sql

SELECT * FROM dbo.TestTable

Returns:

col1    col2
1       1
2       2
3       3

Upvotes: 1

Related Questions