SQL server按月建表并添加数据

create procedure pro_SplitMonthData
as--按月份添加分表
declare @sql nvarchar(2000)			--sql语句字串
declare @Table_Status nvarchar(100)  --ProcessStatus分表名称
declare @Table_Code nvarchar(100)	--ProcessCode分表名称
declare @Table_Data nvarchar(100)	--ProcessData分表名称
declare @CurDate datetime			--系统当前时间
set @CurDate = GETDATE()
set @Table_Status=dbo.fn_GetMonthTableName('ProcessStatus')
set @Table_Code=dbo.fn_GetMonthTableName('ProcessCode')
set @Table_Data=dbo.fn_GetMonthTableName('ProcessData')
begin

    ----ProcessStatus分表
	if not exists(select * from sysobjects where xtype='U' and name =@Table_Status)
	begin
		print '-------------1'
	    --添加分表sql
		set @sql = 'select * into ' + @Table_Status + ' from ProcessStatus where DATEPART(yyyy,create_time) = DATEPART(yyyy,dateadd(m,-1,GETDATE())) and DATEPART(m,create_time) = DATEPART(m,dateadd(m,-1,GETDATE())) ';
		exec sp_executesql @sql;
		print @sql
		print '-------------2'
	end
	else
	begin
	    --删除已有表sql
		print '-------------3'
		set @sql = 'drop table ' + @Table_Status ;
		exec sp_executesql @sql;
		print @sql
		print '-------------4'

		--添加分表sql
		set @sql = 'select * into ' + @Table_Status + ' from ProcessStatus where DATEPART(yyyy,create_time) = DATEPART(yyyy,dateadd(m,-1,GETDATE())) and DATEPART(m,create_time) = DATEPART(m,dateadd(m,-1,GETDATE())) ';
		exec sp_executesql @sql;
		print @sql
		print '-------------5'
	end

	
    ----ProcessCode分表
	if not exists(select * from sysobjects where xtype='U' and name =@Table_Code)
	begin
		print '-------------11'
	    --添加分表sql
		set @sql = 'select * into ' + @Table_Code + ' from ProcessCode where DATEPART(yyyy,create_time) = DATEPART(yyyy,dateadd(m,-1,GETDATE())) and DATEPART(m,create_time) = DATEPART(m,dateadd(m,-1,GETDATE())) ';
		exec sp_executesql @sql;
		print @sql
		print '-------------22'
	end
	else
	begin
	    --删除已有表sql
		print '-------------33'
		set @sql = 'drop table ' + @Table_Code ;
		exec sp_executesql @sql;
		print @sql
		print '-------------44'

		--添加分表sql
		set @sql = 'select * into ' + @Table_Code + ' from ProcessCode where DATEPART(yyyy,create_time) = DATEPART(yyyy,dateadd(m,-1,GETDATE())) and DATEPART(m,create_time) = DATEPART(m,dateadd(m,-1,GETDATE())) ';
		exec sp_executesql @sql;
		print @sql
		print '-------------55'
	end

	
    ----ProcessData分表
	if not exists(select * from sysobjects where xtype='U' and name =@Table_Data)
	begin
		print '-------------111'
	    --添加分表sql
		set @sql = 'select * into ' + @Table_Data + ' from ProcessData where DATEPART(yyyy,create_time) = DATEPART(yyyy,dateadd(m,-1,GETDATE())) and DATEPART(m,create_time) = DATEPART(m,dateadd(m,-1,GETDATE())) ';
		exec sp_executesql @sql;
		print @sql
		print '-------------222'
	end
	else
	begin
	    --删除已有表sql
		print '-------------333'
		set @sql = 'drop table ' + @Table_Data ;
		exec sp_executesql @sql;
		print @sql
		print '-------------444'

		--添加分表sql
		set @sql = 'select * into ' + @Table_Data + ' from ProcessData where DATEPART(yyyy,create_time) = DATEPART(yyyy,dateadd(m,-1,GETDATE())) and DATEPART(m,create_time) = DATEPART(m,dateadd(m,-1,GETDATE())) ';
		exec sp_executesql @sql;
		print @sql
		print '-------------555'
	end


end

create function [dbo].[fn_GetMonthTableName](@TableName nvarchar(100))
returns varchar(100) --根据tablename创建用于存储上个月数据的分表名称，例如：tablename_20230101
as
begin
	declare @BakTableName varchar(100);
	set @BakTableName = @TableName + '_' + LEFT(CONVERT(CHAR(8), dateadd(m,-1,GETDATE()),112),6) 
	return @BakTableName;
end