在项目中经常有大量数据信息保存到数据库，如只用一张表保存那肯定不现实，首选解决方案为按日期建立动态表来保存数据。在不改变保存方式的代码的情况下，用动态存储过程是首选，在sql server存储过程中进行日期计算，按日期建表效率最高，下面就公司项目的部分动态存储过程粘贴出来：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
|
-----sql语句：ALTER proc [dbo].[EventInsert]@chrTagData varchar(50), --编号@intEData int,@chrJZData varchar(50),@intDYData int,@intXHData int,@createdata datetime,@chrtype varchar(1) --查询条件asbegindeclare @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'+@chrdateset @chrSendtabname='MessSend'+@chrdateset @chrSockDatatabname='SockData'+@chrdateif isnull(@chrtype,'')=''beginreturnendselect @chrTitle=CategoryTitle from EventCategory where CategoryID=@chrtype----新建每日信息模拟表1set @chrsql= 'if not exists(select 1 from sysobjects where name='''+@chrMetabname+''' and type=''U'')beginCREATE 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'')beginCREATE TABLE '+@chrSendtabname+'(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'--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'',getdate(),''0''----dbo.funGetEvenAddget 为自定义函数declare @intSMeID intdeclare @chrtempdate varchar(50)set @intSMeID =@@identity delete '+@chrSendtabname+' whereTagData='''+@chrTagData+''' andtype='''+@chrtype+'''---插入表二insert into '+@chrSendtabname+' (TabName,TabPrID,MessTitle,TagData,Content,Type,CreateDate)select '''+@chrMetabname+''',@intSMeID,dbo.funTagDataMeget_all('''+@chrTagData+''')+'''+@chrTitle+''','''+@chrTagData+''',dbo.funTagDataMeget_all('''+@chrTagData+''')+'''+@chrTitle+',位置:[''+dbo.funGetEvenAddget('''+@chrJZData+''')+'']'','''+@chrtype+''',getdate()end 'print @chrsqlexec(@chrsql)end---根据实际业务进行逻辑处理后插入动态表 |
