欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2023-12-25 12:54:39
1.自动建表存储过程 USE [ThreeToOne] GO /****** Object: StoredProcedure [dbo].[WTO_CreateTable_ScanDoXXX] Script Date: 01/08/2019 15:20:09 ******/ SET ANSI_NUL ......

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.配合代理作业定时执行存储过程

 

上一篇:

下一篇: