sql - 使用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^th 值命名表。

Answer 1

是的，这是可以实现的，但是 Curse and Blessing Dynamic SQL by Erland Sommarskog

CREATE TABLE @tbl

The desire here is to create a table of which the name is determined at run-time.

If we just look at the arguments against using dynamic SQL in stored procedures, few of them are really applicable here. If a stored procedure has a static CREATE TABLE in it, the user who runs the procedure must have permissions to create tables, so dynamic SQL will not change anything. Plan caching obviously has nothing to do with it. Etc.

Nevertheless: Why? Why would you want to do this? If you are creating tables on the fly in your application, you have missed some fundamentals about database design. In a relational database, the set of tables and columns are supposed to be constant. They may change with the installation of new versions, but not during run-time.

Sometimes when people are doing this, it appears that they want to construct unique names for temporary tables. This is completely unnecessary, as this is a built-in feature in SQL Server. If you say:

CREATE TABLE #nisse (a int NOT NULL)

then the actual name behind the scenes will be something much longer, and no other connections will be able to see this instance of #nisse.

If you want to create a permanent table which is unique to a user, but you don't want to stay connected and therefore cannot use temp tables, it may be better to create one table that all clients can share, but where the first column is a key which is private to the client. I discuss this method a little more closely in my article How to Share Data between Stored Procedures.

使用内联参数化表值函数的可能解决方案(如果需要，您可以使用存储过程):

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'

编辑

您可以为此使用 View 并将它们传递给用户。如果您仍然想要表随意更改代码，您应该明白这一点。为什么是 View ，因为表仍然是一个，并且您可以获得可以模拟多个表的动态 View 。此外，当主表中的数据更新时，您的所有 View 都将立即获取，无需更新/插入。

SqlFiddleDemo

DBFiddle Demo (updated)

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;