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