sqlsever存储过程配合代理作业自动定时建表

1.自动建表存储过程

USE [ThreeToOne]

GO

/****** Object:  StoredProcedure [dbo].[WTO_CreateTable_ScanDoXXX]    Script Date: 01/08/2019 15:20:09 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author:    LiTiantian

-- Create date: 2018/07/12

-- Description:   

-- =============================================

ALTER PROCEDURE [dbo].[WTO_CreateTable_ScanDoXXX]

    -- Add the parameters for the stored procedure here

    --<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>,

    --<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>

AS

DECLARE @year varchar(4)

DECLARE @month varchar(4)

DECLARE @target varchar(18)

DECLARE @str  varchar(8000)

 

SET @year = datepart(YYYY,GetDate())

SET @month = datepart(week,getdate()) +1

SET @month=replace(right(str(@month),4),' ','0')

 

SET @target= 'ScanDo_' + @year  + @month

 

if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[' + @target + ']') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

 

begin

 

SET @str = '

CREATE TABLE [dbo].[' + @target + '] (

    [ID] [int] IDENTITY(1,1) NOT NULL,

    [FACode] [varchar](50)  NOT NULL,

    [SNCode] [varchar](25) NULL,

    [BiCode] [varchar](25) NULL,

    [MDCode] [varchar](15) NULL,

    [Location] [varchar](50) NULL,

    [CarNum] [varchar](50) NULL,

    [LocalNum] [varchar](50) NULL,

    [FlagFC] [varchar](50) NULL,

    [FlagCL] [varchar](50) NULL,

    [UserID] [varchar](10) NULL,

    [LogonMAC] [varchar](50) NULL,

    [workname] [varchar](20) NULL,

    [WriteDate] [datetime] DEFAULT (CONVERT([varchar],getdate(),(120))) NULL,

    [beforLoca] [varchar](50) NULL,

) ON [PRIMARY]

'

exec (@str)

 

SET @str = 'ALTER TABLE ' + @target + ' ADD PRIMARY KEY (ID,FACode)'

 

exec (@str)

 

end

2.配合代理作业定时执行存储过程

 

转载于:https://www.cnblogs.com/TiantianLi/p/10240875.html