--定义变量
DECLARE @nBase_Id int
DECLARE @stTable_Name nvarchar( 50 )
DECLARE @stSql varchar( 4096 )
--得到插入的基地编号
SELECT @nBase_Id = [Base_Id] FROM inserted
-------------------------创建表格--------------------------------------------
declare @cmd_sql varchar(1500)
set @stTable_Name = 'Data' + convert(varchar,@nBase_Id) ---此处需要注意
------建温湿度历史数据表部分
set @cmd_sql = 'If Not Exists ( select * from dbo.sysobjects where id = object_id(N''' + @stTable_Name + ''' ) and OBJECTPROPERTY(id, N''IsUserTable'') = 1 ) ' +
'begin Create Table ' + @stTable_Name + '(A_Id decimal(18, 0) IDENTITY (1, 1) NOT NULL, '
+ ' Address_Id int NOT NULL DEFAULT (0), '
+ ' Inter_Time datetime NOT NULL DEFAULT (getdate()), '
+ ' Temp_ValueD decimal(18, 1) NOT NULL DEFAULT (0), '
+ ' Wet_ValueD decimal(18, 1) NOT NULL DEFAULT (0), '
+ ' Lux_ValueN int NOT NULL DEFAULT (0), '
+ ' MilliSecond int NULL)'
-----建立索引部分
+ 'Create Clustered Index Data_Inter_Time On ' + @stTable_Name + ' ( Inter_Time )' --- 建立时间聚集索引
+ 'Create NonClustered Index Data_Address_Id On ' + @stTable_Name + '( Address_Id ) end' --- 建立地址ID非聚集索引
-- select len(@cmd_sql) , @cmd_sql
exec (@cmd_sql)