USE [TopDB_Track]
GO
/****** Object: StoredProcedure [dbo].[spSys_CreateDeviceDataTable] Script Date: 03/22/2017 16:31:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spSys_CreateDeviceDataTable]
@TrackDate varchar(8)
AS
BEGIN
--每天创建轨迹表,如果有相同名称的表存在,则报错
declare @TableName varchar(30)
DECLARE @YEAR VARCHAR(4)
DECLARE @MONTH VARCHAR(2)
DECLARE @DATE VARCHAR(2)
declare @SqlStr varchar(8000)
declare @Date_End varchar(20)
set @TableName = 'biz_DevData_'+@TrackDate
SET @YEAR = SUBSTRING(@TrackDate,1,4)
SET @MONTH = SUBSTRING(@TrackDate,5,2)
SET @DATE = SUBSTRING(@TrackDate,7,2)
set @Date_End = convert(char(10),dateadd(day,1,cast(@YEAR+'-'+@MONTH+'-'+@DATE as datetime)),121)
set @SqlStr = 'CREATE TABLE '+@TableName+' (
[SimNum] [varchar](20) NOT NULL,
[GpsTime] [datetime] NOT NULL CHECK([gpstime] >= '''+@YEAR+'-'+@MONTH+'-'+@DATE+''' AND [gpstime] <'''+@Date_End+'''),
[ReceTime] [datetime] NOT NULL,
[Speed] [numeric](5, 1) NOT NULL,
[Mileage] [numeric](18, 2) NULL,
[DeviceType] [int] NOT NULL,
[DeviceId] [int] NOT NULL,
[Value] [varchar](50) NOT NULL,
[PartFlag] [int] NOT NULL,
[Id] [uniqueidentifier] NOT NULL CONSTRAINT DF_'+@TableName+'_Id DEFAULT (NewId()),
CONSTRAINT [PK_'+@TableName+'] PRIMARY KEY CLUSTERED
(
[SimNum] ASC,
[GpsTime] ASC,
[PartFlag] ASC,
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON TrackRangePScheme(PartFlag))
ON TrackRangePScheme(PartFlag)'
print(@SqlStr)
exec(@SqlStr)
END