在SQL循环中执行动态查询

Question

我想在SQL/PDW中的while循环内执行动态查询。此查询将给我最大/最小日期以及Fact表中日期的不同计数。我需要为我的数据库中的所有模式执行此操作（所有模式具有相同的表结构）。

DECLARE @intCNT INT 
SET @intCNT = 1 
DECLARE @strSQL1 VARCHAR(1000) 
DECLARE @strSQL2 VARCHAR(1000) 
DECLARE @strSQL3 VARCHAR(100) 


SET @strSQL1 = 'SELECT MAX(FT_DT) AS MAX_DT, 
        MIN(FT_DT) AS MIN_DT, 
        COUNT(DISTINCT FT_DT) AS DT_CNT 
       FROM ' 

SET @strSQL2 = CONCAT('(SELECT TABLE_SCHEMA 
         FROM 
          (SELECT ROW_NUMBER() OVER(ORDER BY TABLE_SCHEMA) AS ROW_NUM, 
           TABLE_SCHEMA 
          FROM INFORMATION_SCHEMA.TABLES 
          WHERE TABLE_NAME = ''FT_TBL'' 
           AND TABLE_SCHEMA LIKE ''F%'')T 
         WHERE ROW_NUM = ',@intCNT,')') 

SET @strSQL3 = '.FT_TBL' 

EXEC (@strSQL1 + @strSQL2 + @strSQL3) 

当我执行，我得到以下错误：

Msg 102, Level 15, State 1, Line 11 
Incorrect syntax near '.'. 

是否是与语法，我想执行的查询下面给出？我想让我的PDW实例工作。我在SQL Server中尝试过，但得到了同样的错误

Answer 1

你实际上是试图excecute是：从@ strSQL3

SELECT MAX(FT_DT) AS MAX_DT, 
        MIN(FT_DT) AS MIN_DT, 
        COUNT(DISTINCT FT_DT) AS DT_CNT 
       FROM 
(SELECT TABLE_SCHEMA 
         FROM 
          (SELECT ROW_NUMBER() OVER(ORDER BY TABLE_SCHEMA) AS ROW_NUM, 
           TABLE_SCHEMA 
          FROM INFORMATION_SCHEMA.TABLES 
          WHERE TABLE_NAME = 'FT_TBL' 
           AND TABLE_SCHEMA LIKE 'F%')T 
         WHERE ROW_NUM = 1) 
.FT_TBL 

的.FT_TBL摧毁你的查询。也许这应该是

set @strSQL3 = ' FT_TBL' 

Answer 2

您的代码可以用光标来代替用下面的查询，使您能够选择每个模式

DECLARE @Schema_Name VarChar 

DECLARE MY_CURSOR CURSOR 
    LOCAL STATIC READ_ONLY FORWARD_ONLY 
FOR 
SELECT DISTINCT TABLE_SCHEMA 
FROM TABLE_SCHEMA 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_NAME = 'FT_TBL' 
AND TABLE_SCHEMA LIKE 'F%' 

OPEN MY_CURSOR 
FETCH NEXT FROM MY_CURSOR INTO @Schema_Name 
WHILE @@FETCH_STATUS = 0 
BEGIN 
    --Do something with Id here 
    execute CONCAT(CONCAT('SELECT MAX(FT_DT) AS MAX_DT, 
       MIN(FT_DT) AS MIN_DT, 
       COUNT(DISTINCT FT_DT) AS DT_CNT 
      FROM ',@Schema_Name),'FT_TBL') 
    FETCH NEXT FROM MY_CURSOR INTO @Schema_Name 
END 
CLOSE MY_CURSOR 
DEALLOCATE MY_CURSOR