在 SQL Server 中将所有选择查询与动态表名称合并

Question

我有一个带有列 ID 的帐户表，

从所有@ID，并将其另存为查询以运行供以后使用

(我有数千个帐户(

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

我发现了这样的东西来生成一个 flex 查询来检索列 ID 在要执行的 SQL 中，例如

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

我想选择所有帐户，如果它存在，然后合并在一起， 并且会有成千上万的帐户，其中一些存在，有些不存在，

现在在这种情况下，工会不起作用。 有没有其他方法可以做到这一点？非常感谢

Answer 1

不确定"查询供以后使用"是什么意思。您可以使用如下所示的简单查询为所有表生成选择查询 -

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

它将生成如下输出，您可以将其保存在文件中供以后使用。

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 2

这很有趣;我今天刚刚在推特上发布了关于我作为SQL开发人员的20 +年中遇到的最糟糕的数据库设计，这个问题听起来非常相似。 听起来您有一堆除了名称之外完全相同的表，并且名称表示要为其检索数据的实体。 您现在尝试批量执行此操作，因此对于存储在帐户中的每个实体，您希望为这些帐户拉回(我假设(单个数据集。

这可以通过几种不同的方式完成，所有这些都是黑客。 如果你对这个数据库的设计有任何影响，试着想办法重构它。 我为此示例选择了 CURSOR 路由，但也可以使用视图或单个动态 SQL 语句来完成。 但是，这两个选项仅在表数量较少时才有效(我过去使用 90 个表或更少(。 由于我不知道，这里是光标。

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]

长话短说，您循环访问 accounts 表中的每个值并编写动态 SQL 语句。 您可以按 ID 执行它，并将该 SQL 语句的输出插入到临时表中，然后从该临时表中选择结果。

Answer 3

你不能在SQL中参数化对象标识符(即你不能为此使用SqlParameter(，你必须使用"动态SQL" - 这意味着你必须小心避免SQL注入。

假设你必须完全在T-SQL中执行此操作，那么我强烈建议使用SQL Server 2017，因为它引入了非常有用的STRING_AGG函数(相当于MySQL的GROUP_CONCAT函数(。

这样：

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

Answer 4

下面的脚本使用光标

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 5

找到答案

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