使用SQL server将一个主表分成多个表

2021-09-10 00:00:00 sql tsql sql-server

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

I am trying to create multiple tables out of a main table in SQL server. e.g.:

主表看起来像

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"个不同的表，并根据 nth 值命名表.>

The main table is updated every week so can have different alphabets. So I want to create a dynamic query that will automatically divide the main table into 'n' different tables depending on how many different n's are there and also name the table based on the nth value.

推荐答案

是的，这是可以实现的，但是 诅咒和祝福动态 SQL，作者 Erland Sommarskog

Yes it is achievable, but Curse and Blessing Dynamic SQL by Erland Sommarskog

创建表@tbl

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

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

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

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.

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

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:

创建表 #nisse(一个 int NOT NULL)

那么幕后的实际名称将更长，其他任何连接都无法看到#nisse 的这个实例.

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.

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

Possible solution using Inline Parametrized Table-Valued Function (you can use Stored Procedure if needed):

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'

编辑

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

You can use view for this and pass them to users. If you still want tables feel free to change code, you should get the idea. Why views, because table is still one and you get dynamics VIEW that can mimic your multiple tables. Also when data will be updated in main table your all views will get it immediately, no need to update/insert.

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;

相关文章