sqlserver按⽇期建⽴分表的存储过程和调⽤
drop proc proc_CheckEQCurrentDataTableExists
go
--建表过程，先判断表有没有存在，确定要建表，则建表
create proc proc_CheckEQCurrentDataTableExists(@tablename varchar(100),@createTable  bit ,@tbexit bit out,@doresult int out ) as
declare @createsql  varchar(2000)
set @doresult=0
if exists (select top 1 * from sysobjects where id = object_id( ) and xtype='U')
set  @tbexit = 1
else
set  @tbexit=0
if (@tbexit=0 and @createTable=1)
begin
set @createsql = '
CREATE TABLE [dbo].['+ @tablename+'](
[dataid] [bigint] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[Sim] [varchar](12) NULL,
[Commandword] [char](4) NULL,
[clientBeiJinDatetime] [datetime] NULL,
[Valid] [char](1) NULL,
[Latitude] [decimal](18, 4) NULL,
[LatitudeFlag] [char](1) NULL,
[Longitude] [decimal](18, 4) NULL,
[LongitudeFlag] [char](1) NULL,
[Speed] [decimal](18, 2) NULL,
[GreenwichTime] [datetime] NULL,
[Orientation] [decimal](5, 1) NULL,
[IOState] [char](8) NULL,
[Mileage] [decimal](18, 2) NULL,
[platTime] [datetime] NULL,
CONSTRAINT [PK_'+@tablename+'] PRIMARY KEY CLUSTERED
(
[dataid] ASC
)
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
'
exec(@createsql)
set @doresult
end
--创建365张表
go
declare  @i int
declare  @tablename  varchar(50)
declare @tbexit  bit;
declare @result bit;
declare @datename  datetime
declare @errorMsg nvarchar(4000)
set @datename =DATEADD(MM,-1, GETDATE())--提前⼀个⽉
set @i = 1
while(@i<=365)
begin
set @datename  = DATEADD(D,1,@datename);
set @tablename = 'EQCurrentData'+CONVERT(varchar(12) , @datename, 112 );
exec proc_CheckEQCurrentDataTableExists @tablename,1, @tbexit out, @result out
set @i = @i +1
if (@result0)
begin
set @errorMsg  = @errorMsg + '创建表：' + @tablename + '失败；'
end
end
tabletime--显⽰建过程中的错误
select @errorMsg

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。