--建表存储过程
Create Proc [dbo].[PN_CreateHistoryDataTable]
AS
Begin
Declare @Count int
Declare @CollCode varchar(4)
Declare @TableName varchar(30)
Declare @StrSql nvarchar(2000)
Declare @CreateSQL varchar(2000)
Declare CurColliery Cursor For Select CollCode From TN_Colliery
Open CurColliery
Fetch Next From CurColliery Into @CollCode
While @@fetch_status=0
Begin
Set @TableName = 'TN_Data' + @CollCode + Replace(Replace(Replace(Convert(varchar, GetDate(), 112 ),'-',''),' ',''),':','')
Set @StrSql=N'Select @InsideCount=Count(*) From SysObjects Where Id = object_id('''+ @TableName +''') And Type = ''U'''
Exec Sp_ExecuteSQL @StrSql ,N'@InsideCount int output',@Count output
IF(@Count = 0)
Begin
Set @CreateSQL='Create Table '+ @TableName +
'(DataCode varchar(20) Primary Key,
DeviceCode varchar(4) not null,
DeviceName nvarchar(20),
DeviceAddress nvarchar(100),
CollCode varchar(4) not null,
CollName nvarchar(50),
CoalIP varchar(20),
SortName nvarchar(20),
TypeName varchar(20),
DeviceData varchar(4),
TypeUnit varchar(4),
StatusName varchar(50),
LowerLimit varchar(4),
UpperLimit varchar(4),
UploadTime datetime default getdate())'
Exec(@CreateSQL)
End
Fetch Next From CurColliery Into @CollCode
End
Close CurColliery
Deallocate CurColliery
End
--执行语句
exec PN_CreateHistoryDataTable
Create Proc [dbo].[PN_CreateHistoryDataTable]
AS
Begin
Declare @Count int
Declare @CollCode varchar(4)
Declare @TableName varchar(30)
Declare @StrSql nvarchar(2000)
Declare @CreateSQL varchar(2000)
Declare CurColliery Cursor For Select CollCode From TN_Colliery
Open CurColliery
Fetch Next From CurColliery Into @CollCode
While @@fetch_status=0
Begin
Set @TableName = 'TN_Data' + @CollCode + Replace(Replace(Replace(Convert(varchar, GetDate(), 112 ),'-',''),' ',''),':','')
Set @StrSql=N'Select @InsideCount=Count(*) From SysObjects Where Id = object_id('''+ @TableName +''') And Type = ''U'''
Exec Sp_ExecuteSQL @StrSql ,N'@InsideCount int output',@Count output
IF(@Count = 0)
Begin
Set @CreateSQL='Create Table '+ @TableName +
'(DataCode varchar(20) Primary Key,
DeviceCode varchar(4) not null,
DeviceName nvarchar(20),
DeviceAddress nvarchar(100),
CollCode varchar(4) not null,
CollName nvarchar(50),
CoalIP varchar(20),
SortName nvarchar(20),
TypeName varchar(20),
DeviceData varchar(4),
TypeUnit varchar(4),
StatusName varchar(50),
LowerLimit varchar(4),
UpperLimit varchar(4),
UploadTime datetime default getdate())'
Exec(@CreateSQL)
End
Fetch Next From CurColliery Into @CollCode
End
Close CurColliery
Deallocate CurColliery
End
--执行语句
exec PN_CreateHistoryDataTable