您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

SQL Server 動態預存程序按日期分表儲存：每日自動建表、多表協同與業務邏輯封裝

編輯：關於SqlServer

大量資料寫入 (如監控日誌、事件記錄) 採用按日期動態分表 策略，避免單表過大導致效能下降。本文提供完整動態預存程序範例：依日期自動建立三張關聯表 (模擬資料表、訊息彈出表、原始資料表)、條件判斷避免重複插入、動態 SQL 執行建表與插入、自訂函數整合。

核心設計

表名模式	用途	關鍵欄位
SocketMe + yyyyMMdd	模擬/告警資料主表	SMeID (PK), TagData, Type, IfClose, CreateDate
MessSend + yyyyMMdd	訊息彈出/通知表	MessID (PK), TabPrID (FK), TagData, Type
SockData + yyyyMMdd	原始感測/監控資料	依業務定義

動態預存程序完整程式碼

ALTER PROCEDURE [dbo].[EventInsert]
    @chrTagData VARCHAR(50),      -- 編號
    @intEData INT,
    @chrJZData VARCHAR(50),
    @intDYData INT,
    @intXHData INT,
    @createDate DATETIME,
    @chrType VARCHAR(1)           -- 查詢條件/類型
AS
BEGIN
    DECLARE @chrTitle VARCHAR(1000), @chrSql NVARCHAR(4000);
    DECLARE @chrDate VARCHAR(50), @chrMetabName VARCHAR(50);
    DECLARE @chrSendTabName VARCHAR(50), @chrSockDataTabName VARCHAR(50);
    DECLARE @intSMeID INT, @chrTempDate VARCHAR(50);

    IF ISNULL(@chrType, '') = '' RETURN;  -- 類型為空直接返回

    -- 取得分類標題
    SELECT @chrTitle = CategoryTitle FROM EventCategory WHERE CategoryID = @chrType;

    -- 日期字串 (yyyyMMdd)
    SET @chrDate = REPLACE(CONVERT(VARCHAR(10), GETDATE(), 120), '-', '');
    SET @chrMetabName = 'SocketMe' + @chrDate;
    SET @chrSendTabName = 'MessSend' + @chrDate;
    SET @chrSockDataTabName = 'SockData' + @chrDate;

    -- 1. 動態建立主表 (SocketMe)
    SET @chrSql = N'
    IF NOT EXISTS (SELECT 1 FROM sysobjects WHERE name = ''' + @chrMetabName + N''' AND type = ''U'')
    BEGIN
        CREATE TABLE ' + @chrMetabName + N'(
            SMeID INT IDENTITY(1,1) PRIMARY KEY,
            TabName VARCHAR(50), TagData VARCHAR(50), TagDataMe VARCHAR(500),
            Pcount INT NULL, Content VARCHAR(5000), UserID INT NULL,
            JZData VARCHAR(50), EData INT, DYData INT, XHData INT,
            Type VARCHAR(1), State VARCHAR(1), IfClose VARCHAR(1),
            CloseDate DATETIME, CreateDate DATETIME
        )
    END';
    EXEC (@chrSql);

    -- 2. 動態建立訊息表 (MessSend)
    SET @chrSql = N'
    IF NOT EXISTS (SELECT 1 FROM sysobjects WHERE name = ''' + @chrSendTabName + N''' AND type = ''U'')
    BEGIN
        CREATE TABLE ' + @chrSendTabName + N'(
            MessID INT IDENTITY(1,1) PRIMARY KEY,
            TabName VARCHAR(50), TabPrID INT, MessTitle VARCHAR(500),
            TagData VARCHAR(50), TagDataMe VARCHAR(1000), Content VARCHAR(2000),
            Type VARCHAR(1), CreateDate DATETIME
        )
    END';
    EXEC (@chrSql);

    -- 3. 條件插入邏輯 (避免重複未關閉事件)
    SET @chrSql = N'
    IF NOT EXISTS (SELECT 1 FROM ' + @chrMetabName + N' WHERE TagData = ''' + @chrTagData + N''' AND Type = ''' + @chrType + N''' AND IfClose = ''0'')
    BEGIN
        -- 插入主表
        INSERT INTO ' + @chrMetabName + N' (TabName, TagData, TagDataMe, Content, JZData, EData, DYData, XHData, Type, IfClose, CreateDate, State)
        SELECT ''' + @chrMetabName + N''', ''' + @chrTagData + N''', dbo.funTagDataMeget_all(''' + @chrTagData + N'''), 
               ''' + @chrTitle + N',位置:['' + dbo.funGetEvenAddget(''' + @chrJZData + N''') + '']'',
               ''' + @chrJZData + N''', ' + CAST(@intEData AS VARCHAR) + N', ' + CAST(@intDYData AS VARCHAR) + N', ' + CAST(@intXHData AS VARCHAR) + N',
               ''' + @chrType + N''', ''0'', GETDATE(), ''0'';

        -- 取得新建身分識別值
        DECLARE @intSMeID INT = @@IDENTITY;

        -- 清理舊訊息並插入新訊息
        DELETE ' + @chrSendTabName + N' WHERE TagData = ''' + @chrTagData + N''' AND Type = ''' + @chrType + N''';
        INSERT INTO ' + @chrSendTabName + N' (TabName, TabPrID, MessTitle, TagData, Content, Type, CreateDate)
        SELECT ''' + @chrMetabName + N''', @intSMeID,
               dbo.funTagDataMeget_all(''' + @chrTagData + N''') + ''' + @chrTitle + N''',
               ''' + @chrTagData + N''',
               dbo.funTagDataMeget_all(''' + @chrTagData + N''') + ''' + @chrTitle + N',位置:['' + dbo.funGetEvenAddget(''' + @chrJZData + N''') + '']'',
               ''' + @chrType + N''', GETDATE();
    END';
    EXEC (@chrSql);
END
GO