使用SQL Server将主表划分为多个表

Question

我正在尝试从SQL Server的主表中创建多个表。例如：

主表看起来像

A 1
A 2
A 3
B 4
B 5
B 6

输出应如下所示：

表A：

A 1
A 2
A 3

表B：

B 4 
B 5 
B 6

主表每周更新一次，因此可以使用不同的字母。因此，我想创建一个动态查询，该查询将根据有多少个不同的n自动将主表分为“ n”个不同的表，并根据第n个值命名该表。

Answer 1

是的，它是可以实现的，但是Erland
Sommarskog的《诅咒与祝福》动态SQL

创建表@tbl

这里的愿望是创建一个表，该表的名称在运行时确定。

如果我们只看一下反对在存储过程中使用动态SQL的论点，那么其中很少有真正适用的论点。如果存储过程中具有静态CREATE
TABLE，则运行该过程的用户必须具有创建表的权限，因此动态SQL不会更改任何内容。计划缓存显然与它无关。等等。

但是：为什么呢？你为什么想做这个？ 如果要在应用程序中动态创建表，那么您将错过有关数据库设计的一些基础知识。
在关系数据库中，表和列的集合应该是恒定的。它们可能会随着新版本的安装而发生变化，但不会在运行时发生变化。

有时，当人们这样做时，似乎他们想为临时表构造唯一的名称。这是完全不必要的，因为这是SQL Server的内置功能。如果你说：

创建表#nisse（一个int NOT NULL）

那么幕后的实际名称将更长一些，并且其他连接都将无法看到此#nisse实例。

如果要创建一个用户唯一的永久表，但又不想保持连接状态，因此不能使用临时表，那么最好创建一个所有客户端都可以共享的表，但是在第一列是客户端专用的密钥。我将在文章如何在存储过程之间共享数据中更详细地讨论此方法。

使用内联参数化表值函数的可能解决方案（如果需要，可以使用存储过程）：

CREATE FUNCTION dbo.fxnExample (@Parameter1 NVARCHAR(1))
RETURNS TABLE
AS
RETURN
(
    SELECT id, value
    FROM TableName
    WHERE id = @Parameter1
)

-- Usage Example
SELECT * FROM dbo.fxnExample('A')   -- only data from 'A'
SELECT * FROM dbo.fxnExample('B')   -- only data from 'B'

编辑

您可以为此使用视图，并将它们传递给用户。如果您仍然希望表可以随意更改代码，那么您应该了解一下。为什么要使用视图，因为表仍然是其中之一，因此您会获得可以模仿多个表的动态视图。同样，当数据将在主表中更新时，您的所有视图都将立即获取它，而无需更新/插入。

SqlFiddleDemo

DBFiddle演示（已更新）

CREATE TABLE main_tab(suffix NVARCHAR(10) NOT NULL, val INT);

INSERT INTO main_tab(suffix, val)
VALUES ('A', 1), ('A', 2), ('A', 3),
       ('B', 4), ('B', 5), ('B', 6),
       ('C', 7), ('C', 8), ('C', 9);


/* Get list of suffixes */
SELECT suffix,
      [row_id] = ROW_NUMBER() OVER(ORDER BY suffix)
INTO #temp
FROM main_tab
GROUP BY suffix;

DECLARE @name_suffix NVARCHAR(100),
        @sql NVARCHAR(MAX),
        @view_name NVARCHAR(MAX),
        @index INT = 1,
        @total INT = (SELECT COUNT(*) FROM #temp);

/* I used simple while loop but you can change to CURSOR if needed */
WHILE (@index <= @total)
BEGIN

   SELECT @name_suffix = suffix 
   FROM #temp 
   WHERE row_id = @index;

   SELECT @sql = 
          N'CREATE VIEW [dbo].[View@name_suffix]
          AS
          SELECT
             t.suffix,
             t.val
          FROM [dbo].[main_tab] t
          WHERE t.suffix = ''@name_suffix''
          WITH CHECK OPTION'

   SELECT 
        @view_name = REPLACE('[dbo].[View@name]', '@name', @name_suffix)
       ,@sql = REPLACE(@sql, '@name_suffix', @name_suffix)

   /* Check if view exists, if not create one */
   /* Instead of EXEC you can use EXEC [dbo].[sp_executesql]
       and pass params explicitly */
   IF OBJECT_ID(@view_name, 'V') IS NULL            
       EXEC(@sql)

   SET @index += 1;
END

/* Check if you can query views */
SELECT *
FROM ViewA;

SELECT *
FROM ViewB;

SELECT *
FROM ViewC;