0

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.

Eric Brandt
  • 7,886
  • 3
  • 18
  • 35
Liz
  • 1
  • Does the base table name stay the same, except for the date extensions? – Eric Brandt Jul 11 '19 at 18:06
  • 1
    You will need to do this with dynamic sql. That said this is probably an XY problem, as a database that is constantly adding new tables with the same layout is probably not the best solution. – Tab Alleman Jul 11 '19 at 18:18
  • You want to use a query (string) from your app, a sql view, a stored procedure, a table-valued function? From a stored procedure, it is possible to do want you want (but I'm not sure if my way is the best way). – DanB Jul 11 '19 at 18:18
  • Possible duplicate of [Perform Union if table with these names exist](https://stackoverflow.com/questions/56965616/perform-union-if-table-with-these-names-exist) – SMor Jul 11 '19 at 18:18
  • 1
    Now would be a good time to discuss this situation with your team. Sounds like a process / implementation issue that might be better addressed by not creating what appear to be duplicate tables. – SMor Jul 11 '19 at 18:20

1 Answers1

1

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
Max Szczurek
  • 4,324
  • 2
  • 20
  • 32