在项目中经常有大量数据信息保存到数据库，如只用一张表保存那肯定不现实，首选解决方案为按日期建立动态表来保存数据。在不改变保存方式的代码的情况下，用动态存储过程是首选，在sql server存储过程中进行日期计算，按日期建表效率最高，下面就公司项目的部分动态存储过程粘贴出来：

ALTER proc [dbo].[EventInsert]
@chrTagData varchar(50),–编号
@intEData int,@chrJZData varchar(50),@intDYData int,@intXHData int,@createdata datetime,@chrtype varchar(1) –查询条件
as
begin

declare @chrTitle varchar(1000)
declare @chrSql nvarchar(4000)
declare @chrdate varchar(50)
declare @chrMetabname varchar(50) –每日新建报警新表名
declare @chrSendtabname varchar(50) –每日新建消息弹出框新表名
declare @chrSockDatatabname varchar(50) –每日原始数据新表名

set @chrdate =replace(convert(varchar(10),getdate(),120),’-‘,”)

set @chrMetabname=’SocketMe’+@chrdate
set @chrSendtabname=’MessSend’+@chrdate
set @chrSockDatatabname=’SockData’+@chrdate

if isnull(@chrtype,”)=”
begin
return
end

select @chrTitle=CategoryTitle from EventCategory where CategoryID=@chrtype

—-新建每日信息模拟表1

set @chrsql= ‘
if not exists(select 1 from sysobjects where name=”’+@chrMetabname+”’ and type=”U”)
begin
CREATE TABLE ‘+@chrMetabname+'(
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
‘
–print @chrsql
exec(@chrsql)

——–新建信息模拟表2————

set @chrsql= ‘
if not exists(select 1 from sysobjects where name=”’+@chrSendtabname+”’ and type=”U”)
begin
CREATE TABLE ‘+@chrSendtabname+'(
MessID int IDENTITY(1,TabName varchar(50),TabPrID int,MessTitle varchar(500),TagDataMe varchar(1000),Content varchar(2000),CreateDate datetime
)
end
‘
–print @chrsql
exec(@chrsql)

—–模拟环境 判断符合条件的数据则插入———————-

set @chrsql= ‘
if not exists(select 1 from ‘+@chrMetabname+’ whereTagData=”’+@chrTagData+”’ and type=”’+@chrtype+”’ and IfClose=”0”)
begin

–插入表一

insert into ‘+@chrMetabname+’ (tabname,TagData,TagDataMe,Content,JZData,EData,DYData,XHData,Type,IfClose,CreateDate,State)

–模拟数据

select ”’+@chrMetabname+”’,”’+@chrTagData+”’,dbo.funTagDataMeget_all(”’+@chrTagData+”’),”’+@chrTitle+’,位置:[”+dbo.funGetEvenAddget(”’+@chrJZData+”’)+”]”,”’+@chrJZData+”’,”’+CAST(@intEData as varchar)+”’,”’+CAST(@intDYData as varchar)+”’,”’+CAST(@intXHData as varchar)+”’,”’+@chrtype+”’,”0”,”0”

—-dbo.funGetEvenAddget 为自定义函数

declare @intSMeID int
declare @chrtempdate varchar(50)
set @intSMeID =@@identity

delete ‘+@chrSendtabname+’ whereTagData=”’+@chrTagData+”’ andtype=”’+@chrtype+”’

—插入表二

insert into ‘+@chrSendtabname+’ (TabName,TabPrID,MessTitle,CreateDate)
select ”’+@chrMetabname+”’,@intSMeID,dbo.funTagDataMeget_all(”’+@chrTagData+”’)+”’+@chrTitle+”’,dbo.funTagDataMeget_all(”’+@chrTagData+”’)+”’+@chrTitle+’,getdate()

end
‘
print @chrsql
exec(@chrsql)

end

—根据实际业务进行逻辑处理后插入动态表