Union all select query with a dynamic table name in SQL Server

Question

I have an account table with column ID,

from all the @ID, and save it as a query to run for later use

(I have thousand of accounts)

ID
------------------------------------
8C76EF27-3080-4DAA-881B-08CD2A1A558F
62FFCB40-AAB4-47A5-953A-08CD2A1A6A43
CFFD7C3C-FEFC-4E97-9970-08CD2A1A3A60
0F5ADAF3-32EF-4D43-BFBD-08CD2A1A5D88
4130153C-24C1-4914-A6F1-08CD2A16DF59

I have found something like this to generate a flex query to retrieve column ID in the sql to be executed like

with getAllAccount as(
select B.ID,B.DisplayName from (
select ID,DisplayName from u3_system.[dbo].[Account] with (nolock)
where ID = '8c76ef27-3080-4daa-881b-08cd2a1a555f' or ParentID = '8c76ef27-3080-4daa-881b-08cd2a1a555f') A
join u3_system.[dbo].[Account] B with (nolock)
on A.ID = B.ParentID
union 
select ID,DisplayName from u3_system.[dbo].[Account] with (nolock)
where ID = '8c76ef27-3080-4daa-881b-08cd2a1a555f'
)

--create pre script
Select 'if exists (select * from sys.tables where name = ''Maillog_' + replace(getAllAccount.ID,'-','') + ') begin Select CampaignID,mailoutID,deliveryDate from  u3_data.data.Maillog_' + replace(getAllAccount.ID,'-','') + ' with(nolock) end union '
from getAllAccount

I want to select all the account if its exists then union together, and there will be thousands of account, some of them exists, some not,

Now the union in this case doesnt work. Is there another way to do it? thanks very much

Answer 1

Found the answer

Select * from 
(   Select case when exists (select * from sys.tables where name = 'Maillog_' + replace(getAllAccount.ID,'-',''))
        Then 'Select CampaignID,mailoutID,deliveryDate from  u3_data.data.Maillog_' + replace(getAllAccount.ID,'-','') + ' with(nolock) union ' End execQuery
    from getAllAccount
) A
where execQuery is not null

Answer 2

It's funny; I just tweeted today about the worst database design I've ever encountered in my 20+ years as a SQL developer, and this question sounds eerily similar. It sounds like you have a bunch of tables that are identical except for the name, and the name represents an entity that you want to retrieve data for. You're now trying to do it as a batch, so for every entity stored in accounts, you want to pull back a (I assume) single data-set for those accounts.

This can be done in a couple of different ways, all of them hackish. If you have any influence over the design of this database, try to figure out a way to refactor it. I chose the CURSOR route for this sample, but it might also be done using a view or a single dynamic SQL statement. Those two options, however, will only work if the number of tables are small (I've done it with 90 tables or less in the past). Since I don't know that, here's the CURSOR.

 USE tempdb;

/*set up demo up here*/
CREATE TABLE Account (ID uniqueidentifier)

INSERT INTO Account (ID)
VALUES ('8C76EF27-3080-4DAA-881B-08CD2A1A558F'),
('62FFCB40-AAB4-47A5-953A-08CD2A1A6A43'),
('CFFD7C3C-FEFC-4E97-9970-08CD2A1A3A60'),
('0F5ADAF3-32EF-4D43-BFBD-08CD2A1A5D88'),
('4130153C-24C1-4914-A6F1-08CD2A16DF59')

--these are not temp tables; be sure to clean up.  naming has hyphens
CREATE TABLE [MT_4130153C-24C1-4914-A6F1-08CD2A16DF59] (val varchar(20))
CREATE TABLE [MT_CFFD7C3C-FEFC-4E97-9970-08CD2A1A3A60] (val varchar(20))
CREATE TABLE [MT_8C76EF27-3080-4DAA-881B-08CD2A1A558F] (val varchar(20))
CREATE TABLE [MT_0F5ADAF3-32EF-4D43-BFBD-08CD2A1A5D88] (val varchar(20))
CREATE TABLE [MT_62FFCB40-AAB4-47A5-953A-08CD2A1A6A43] (val varchar(20))

INSERT INTO [MT_4130153C-24C1-4914-A6F1-08CD2A16DF59] VALUES ('This')
INSERT INTO [MT_CFFD7C3C-FEFC-4E97-9970-08CD2A1A3A60] VALUES ('is')
INSERT INTO [MT_8C76EF27-3080-4DAA-881B-08CD2A1A558F] VALUES ('a')
INSERT INTO [MT_0F5ADAF3-32EF-4D43-BFBD-08CD2A1A5D88] VALUES ('bad')
INSERT INTO [MT_62FFCB40-AAB4-47A5-953A-08CD2A1A6A43] VALUES ('design')


/*magic starts here*/
CREATE TABLE #output (val varchar(20), id uniqueidentifier)

DECLARE @sql nvarchar(200), @Id varchar(50)

DECLARE c CURSOR FOR
SELECT CONVERT(VARCHAR(50), ID) FROM account 

OPEN C

FETCH NEXT FROM C INTO @ID
WHILE @@FETCH_STATUS = 0
BEGIN 

SET @SQL = 'SELECT val, ''' + @ID + ''' FROM [MT_' + @ID + ']'      

INSERT INTO #output (val, id)
exec sp_executesql @SQL


FETCH NEXT FROM C INTO @ID
END

CLOSE C
DEALLOCATE C


/*output comes next*/
SELECT *
FROM #output
ORDER BY ID

DROP TABLE #output


/*clean up demo*/
DROP TABLE Account

DROP TABLE [MT_4130153C-24C1-4914-A6F1-08CD2A16DF59]
DROP TABLE [MT_CFFD7C3C-FEFC-4E97-9970-08CD2A1A3A60]
DROP TABLE [MT_8C76EF27-3080-4DAA-881B-08CD2A1A558F]
DROP TABLE [MT_0F5ADAF3-32EF-4D43-BFBD-08CD2A1A5D88]
DROP TABLE [MT_62FFCB40-AAB4-47A5-953A-08CD2A1A6A43]

Long story short, you iterate through each value in the accounts table and write a dynamic SQL statement. You execute it per ID, and insert the output of that SQL statement into a temporary table, and then select the results from that temp table.

Answer 3

Below Script using Cursor

create table tbl(ID varchar(max))
insert into tbl values('8C76EF27-3080-4DAA-881B-08CD2A1A558F')
insert into tbl values('62FFCB40-AAB4-47A5-953A-08CD2A1A6A43')
insert into tbl values('CFFD7C3C-FEFC-4E97-9970-08CD2A1A3A60')
insert into tbl values('0F5ADAF3-32EF-4D43-BFBD-08CD2A1A5D88')
insert into tbl values('4130153C-24C1-4914-A6F1-08CD2A16DF59')


DECLARE @sql varchar(max)
SELECT  CONCAT('SELECT * FROM ',[ID], CHAR(13) + CHAR(10)  ) as execRecord
into #tmp
from tbl
select * from #tmp

    DECLARE ID_Cursor CURSOR FOR
        SELECT execRecord from #tmp
    DECLARE @tmpID varchar(max)
    OPEN ID_Cursor   
        FETCH NEXT FROM ID_Cursor INTO @tmpID
        WHILE @@FETCH_STATUS = 0   
        BEGIN   
            SET @sql = @tmpID
            EXECUTE SP_EXECUTESQL @sql
            SET @sql = ''
            FETCH NEXT FROM ID_Cursor INTO @tmpID  
        END   

    CLOSE ID_Cursor   
    DEALLOCATE ID_Cursor

DROP TABLE #tmp
DROP TABLE tbl

Answer 4

Not sure what do you mean by "query for later use". You can generate select queries for all tables with a simple query like below -

Select CONCAT('select * from  u5_data.data.Mailtable_', ID)  from Account

It will generate the output as below, which you can save it in file for later use.

select * from  u5_data.data.Mailtable_8C76EF27-3080-4DAA-881B-08CD2A1A558F
select * from  u5_data.data.Mailtable_62FFCB40-AAB4-47A5-953A-08CD2A1A6A43
select * from  u5_data.data.Mailtable_CFFD7C3C-FEFC-4E97-9970-08CD2A1A3A60
select * from  u5_data.data.Mailtable_0F5ADAF3-32EF-4D43-BFBD-08CD2A1A5D88
select * from  u5_data.data.Mailtable_4130153C-24C1-4914-A6F1-08CD2A16DF59

Answer 5

You cannot parameterise object identifiers in SQL (i.e. you cannot use SqlParameter for this), you must use "Dynamic SQL" - which means you have to be careful to avoid SQL Injection.

Assuming you have to do this entirely in T-SQL, then I strongly recommend using SQL Server 2017 because it introduces the very useful STRING_AGG function (equivalent to MySQL's GROUP_CONCAT function).

Like so:

DECLARE @sql varchar(max)

SELECT
    @sql = STRING_AGG( CONCAT( 'SELECT * FROM ', [ID] ), CHAR(13) + CHAR(10) )
FROM
    Accounts

EXECUTE sp_executesql @sql