创建日期命名的表名，并转移已完成的数据

/* Object: StoredProcedure [dbo].[CreateTable] Script Date: 2017/6/20 9:58:15 */
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
– =============================================
– Author:
– Create date: <2017-6-20>
– Description: Nginx表自动创建并转移已完成的数据
– =============================================
ALTER PROCEDURE [dbo].[CreateTable]
– Add the parameters for the stored procedure here

--定义日期参数，即“N”天之前的日期
@Day int

AS

IF(SELECT COUNT(0) FROM NginxLog
–WHERE IsGrab=1 AND IsRead=’2’ AND IsRanking=’1’
)>0
BEGIN

–第一部分：以NginxLog_加“N天”之前的日期，自动创建带日期命名的表，如：NginxLog_20170614

--定义出“N天”之前的表拼接名
DECLARE @NDay VARCHAR(10)
SELECT  @NDay = CONVERT(CHAR(8),GETDATE()-@Day,112)
Declare @TableDay VARCHAR(20) = 'NginxLog_'+@NDay
SELECT @TableDay

--创建日期命名的表,将要做的SQL操作全部存储到变量
DECLARE @sql_str1 NVARCHAR(MAX)
SET @sql_str1 = N'
CREATE TABLE [dbo].['+@TableDay+'](
[Id] [nvarchar](36) NOT NULL,
[Ip] [nvarchar](15) NOT NULL,
[AccessTime] [datetime] NOT NULL,
[RemoteType] [nvarchar](20) NOT NULL,
[Uri] [nvarchar](4000) NOT NULL,
[RemoteProtocol] [nvarchar](10) NOT NULL,
[ResponseState] [int] NOT NULL,
[BandWidth] [bigint] NOT NULL,
[Os] [nvarchar](2000) NOT NULL,
[HttpReferer] [nvarchar](255) NOT NULL,
[HttpType] [nvarchar](5) NOT NULL,
[ResponseTime] [float] NULL CONSTRAINT [DF_NginxLog_ResponseTime'+@NDay+']  DEFAULT ((0)),
[AccessMachine] [nvarchar](15) NOT NULL CONSTRAINT [DF_NginxLog_AccessMachine'+@NDay+']  DEFAULT (NULL),
[IpProvince] [nvarchar](50) NULL,
[IpCity] [nvarchar](50) NULL,
[IsRead] [tinyint] NOT NULL CONSTRAINT [DF__NginxLog__isRead__2B3F6F97'+@NDay+']  DEFAULT ((0)),
[IsGrab] [tinyint] NOT NULL CONSTRAINT [DF_NginxLog_IsGrab'+@NDay+']  DEFAULT ((0)),
[UpstreamTime] [float] NULL CONSTRAINT [DF_NginxLog_UpstreamTime'+@NDay+']  DEFAULT ((0)),
[IsRanking] [tinyint] NOT NULL CONSTRAINT [DF__NginxLog__IsRank__5B78929E'+@NDay+']  DEFAULT ((0)),
CONSTRAINT [PK__tmp_ms_x__3214EC0724D4C13B'+@NDay+'] PRIMARY KEY CLUSTERED 
(
[Id] 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 (@sql_str1)

–第二部分：当数据库的日期=N天前定义的日期，则将数据导入到“NginxLog_日期”的表中

--定义判断条件的日期
DECLARE @jDay CHAR(10)='0000-00-'+CAST(@Day AS CHAR(10))
SELECT @jDay

DECLARE @sql_str2 NVARCHAR(MAX)
SET @sql_str2 = N'
INSERT INTO [dbo].['+@TableDay+'](
   [Id]
  ,[Ip]
  ,[AccessTime]
  ,[RemoteType]
  ,[Uri]
  ,[RemoteProtocol]
  ,[ResponseState]
  ,[BandWidth]
  ,[Os]
  ,[HttpReferer]
  ,[HttpType]
  ,[ResponseTime]
  ,[AccessMachine]
  ,[IpProvince]
  ,[IpCity]
  ,[IsRead]
  ,[IsGrab]
  ,[UpstreamTime]
  ,[IsRanking]
  )
 SELECT 
  [Id]
  ,[Ip]
  ,[AccessTime]
  ,[RemoteType]
  ,[Uri]
  ,[RemoteProtocol]
  ,[ResponseState]
  ,[BandWidth]
  ,[Os]
  ,[HttpReferer]
  ,[HttpType]
  ,[ResponseTime]
  ,[AccessMachine]
  ,[IpProvince]
  ,[IpCity]
  ,[IsRead]
  ,[IsGrab]
  ,[UpstreamTime]
  ,[IsRanking]
  FROM dbo.NginxLog WHERE CONVERT(char(10),AccessTime,120)=CONVERT(char(10),getdate()-'+@jDay+',120)'

  --执行SQL
  EXEC (@sql_str2)  

–第三部分：删除NginxLog表中，这一天的数据
SELECT * FROM dbo.NginxLog WHERE CONVERT(char(10),AccessTime,120)=CONVERT(char(10),getdate()-@Day,120)
–DELETE FROM dbo.NginxLog WHERE CONVERT(char(10),AccessTime,120)=CONVERT(char(10),getdate()-@Day,120)
END

ELSE
PRINT ‘没有满足条件的表创建!’