問題描述

我正在嘗試從 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"個不同的表，并根據 n^th 值命名表.>

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 n^th 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;

這篇關于使用SQL server將一個主表分成多個表的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！