【无标题】SQL数据库动态生成月份表A，并从源数据B表插入数据然后删除B表数据（数据分区存储）

本文链接：https://blog.csdn.net/u013597888/article/details/126289033

CREATE PROCEDURE [dbo].[maintain_DataCsvTable]
AS
BEGIN
declare @tablename nvarchar(50)
set @tablename='DataCsv'
--set @tablename=@tablename+cast(datepart(year,GETDATE()) as nvarchar(50))+cast(DATEPART(MONTH,GETDATE()) as nvarchar(50))
SET @tablename = @tablename+LEFT(CONVERT(CHAR(8), GETDATE(),112),6)
--select @tablename
execute ('CREATE TABLE '+@tablename+'(
[DataCsvId] [char](12) NOT NULL,
[HostNumber] [varchar](100) NULL,
[DetectionTime] [datetime] NULL,
[ProductQRCode] [varchar](500) NULL,
[ProductCode] [varchar](500) NULL,
[TestTtems] [varchar](500) NULL,
[RelativeVUnit] [varchar](50) NULL,
[AbsoluteVUnit] [varchar](50) NULL,
[UpperLimit] [decimal](18, 4) NULL,
[LowerLimit] [decimal](18, 4) NULL,
[NominalValue] [decimal](18, 4) NULL,
[RelatValue] [decimal](18, 4) NULL,
[AbsoluteValue] [decimal](18, 4) NULL,
[States] [char](20) NULL, ) ON [PRIMARY] ')
END.

/****** Object: StoredProcedure [db_accessadmin].[maintain_DataCsvs] Script Date: 2022/8/10 21:55:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [db_accessadmin].[maintain_DataCsvs]
@I_ReturnMessage NVARCHAR(MAX)='' OUTPUT --返回的信息,支持多语言
AS
BEGIN
--定义变量@sql,@firstday,@lastday
       DECLARE @Sql NVARCHAR(MAX)
       DECLARE @firstday DATETIME
       DECLARE @lastday DATETIME
       SET @firstday=CONVERT(VARCHAR(10),DATEADD(DAY,-DATEPART(DAY,GETDATE())+1,GETDATE()),23) +' 00:00:00' --当月第一天
       SET @lastday=CONVERT(VARCHAR(10),DATEADD(DAY,-1,DATEADD(MONTH,1,GETDATE()-DAY(GETDATE())+1)),23)+' 23:59:59' --当月最后一天
       --判断当前日期是否为当月1日，如果是则执行创建新表
       DECLARE @days CHAR(2)
       DECLARE @tablename NVARCHAR(MAX)
       SELECT @days = RIGHT(CONVERT(CHAR(8), GETDATE(),112),2)
       IF @days = '01'
           BEGIN
               SET @tablename='DataCsv'
               SET @tablename = @tablename+LEFT(CONVERT(CHAR(8), GETDATE(),112),6)
               IF NOT EXISTS(SELECT 1 FROM SysObjects WHERE type='U' AND [name] = @tablename)
               BEGIN
                   EXEC [dbo].[maintain_DataCsvTable]
               END
           END
       --查询数据库是否存在表
      IF ISNULL(@tablename,'')<>''
           --将数据插入数据库表（表名为动态表名@tablename）
           BEGIN
               SET @Sql='insert into '''+@tablename+''' SELECT DataCsvId,HostNumber,DetectionTime,ProductQRCode,ProductCode,TestTtems,RelativeVUnit,AbsoluteVUnit,UpperLimit,LowerLimit,NominalValue,RelatValue,AbsoluteValue,States from dbo.DataCsv WHERE DetectionTime BETWEEN '''+CONVERT(VARCHAR(30),@firstday,21)+''' AND '''+CONVERT(VARCHAR(30),@lastday,21)+''' ORDER BY DetectionTime ASC'
               EXEC (@Sql)
               ----运行结束以后，反馈信息
               SELECT @@rowcount -- 返回影响记录的行数
               IF @@error <> 0 or @@rowcount <> 1
                   BEGIN
                       raiserror('新数据表插入数据失败',16,1)
                   END
ELSE
               BEGIN
                   RAISERROR('新数据表插入数据成功',16,1)
                   DELETE FROM dbo.DataCsv WHERE DetectionTime BETWEEN @firstday AND @lastday --删除DataCsv表中的数据
                   SELECT @@rowcount -- 返回影响记录的行数
                   RAISERROR('删除原表数据成功',16,1)
                   END
           END
       ELSE
       --新建DATACSV表（表名格式DataCsv+年+月）
           IF ISNULL(@tablename,'')=''
               BEGIN
                   SET @tablename='DataCsv'
                   SET @tablename = @tablename+LEFT(CONVERT(CHAR(8), GETDATE(),112),6)
                   IF NOT EXISTS(SELECT 1 FROM SysObjects WHERE type='U' AND [name] = @tablename)
                   BEGIN
                       EXEC [dbo].[maintain_DataCsvTable]
                   END
               END
           BEGIN
       --将数据插入数据库表（表名为动态表名@tablename）
               SET @Sql='insert into '+@tablename+' SELECT DataCsvId,HostNumber,DetectionTime,ProductQRCode,ProductCode,TestTtems,RelativeVUnit,AbsoluteVUnit,UpperLimit,LowerLimit,NominalValue,RelatValue,AbsoluteValue,States from dbo.DataCsv WHERE DetectionTime BETWEEN '''+CONVERT(VARCHAR(30),@firstday,21)+''' AND '''+CONVERT(VARCHAR(30),@lastday,21)+''' ORDER BY DetectionTime ASC'
           EXEC (@Sql)
               SELECT @@rowcount -- 返回影响记录的行数
               IF @@error <> 0 or @@rowcount <> 1
                   BEGIN
                       raiserror('新数据表插入数据失败',16,1)
                   END
ELSE
               BEGIN
                   RAISERROR('新数据表插入数据成功',16,1)
                   DELETE FROM dbo.DataCsv WHERE DetectionTime BETWEEN @firstday AND @lastday --删除DataCsv表中的数据
                   SELECT @@rowcount -- 返回影响记录的行数
                   RAISERROR('删除原表数据成功',16,1)
                   END

       END

END