SQL Server Job usage

I.Application of jobs (automatic updateDB)

1,OpenSQL ServerEnterprise Manager, find the job in the Management

2,Add a job, set specific settings, and add steps to confirmSPAndSQLStatement

Add scheduling to determine the frequency of execution!
3Examples

II.ProcessingSPAnd othersSQLStatement for job processing (using the Data Warehouse Mode)

1,Create a space for storing data(Generally table)

2,Use jobs for data Filling

3,Present data through stored data

III.Example

1,Create a table

If exists (select * From DBO. sysobjects where id = object_id (n' [DBO]. [r_count1] ')

And objectproperty (ID, N 'isusertable') = 1)

Drop table [DBO]. [r_count1]

Go

Create Table [DBO]. [r_count1] (

[Unitcoding] [varchar] (15) Collate chinese_prc_ci_as not null,

[Statdate] [varchar] (10) Collate chinese_prc_ci_as not null,

[Field1] [int] not null,

[Field2] [int] not null,

[Field3] [int] not null,

[Field4] [int] not null,

[Field5] [int] not null,

[Field6] [int] not null,

[Field7] [int] not null,

[Field8] [int] not null,

[Field9] [int] not null,

[Field10] [int] not null,

[Field11] [int] not null,

[Field12] [int] not null,

[Field13] [int] not null,

[Field14] [int] not null,

[Field15] [int] not null,

[Field16] [int] not null,

[Field17] [int] not null,

[Field18] [int] not null

) On [primary]

Go

2,Process Data(OperationsSP)

AlterProcedure autoexec_count1

(

@ Unitcoding varchar (20 ),

@ Statdate datetime

)

As

 

 

/*Insert the new and old units to be changed to the temporary tableCode*/

Declare @ I _sfirstdate varchar (10)

Declare @ I _efirstdate varchar (10)

Declare @ I _sseconddate varchar (10)

Declare @ I _eseconddate varchar (10)

 

Declare @ I _getdate datetime

Select @ I _getdate = @ statdate

Select @ I _sfirstdate = DBO. u_date2char (dateadd (mm, datediff (mm, 0, @ I _getdate), 0 ))--Day of the current month1Days

Select @ I _efirstdate = DBO. u_date2char (dateadd (DD, 14, dateadd (mm, datediff (mm, 0, @ I _getdate), 0 )))--Day of the current month15Days

Select @ I _sseconddate = DBO. u_date2char (dateadd (DD, 15, dateadd (mm, datediff (mm, 0, @ I _getdate), 0 )))--Day of the current month16Days

Select @ I _eseconddate = DBO. u_date2char (dateadd (MS,-3, dateadd (mm, datediff (M, 0, @ I _getdate) + )))--Last month1Days

/*Start transaction*/

Begin transaction

 

/*Create a temporary table for saving the unit code and report cycle*/

Create Table # tblunitdateswap

(

Numberid int identity (1, 1) not null,

Unitcoding varchar (20 ),

Startdate varchar (10 ),

Enddate varchar (10)

)

 

/*Insert unit data to the temporary table*/

Select @ unitcoding = rtrim (@ unitcoding) + '% '--

--Insert the statistical date of the last half month(Example: 2004-1-1 ~ 2004-1-15)

Insert into # tblunitdateswap (unitcoding, startdate, enddate)

Select unitcoding, @ I _sfirstdate, @ I _efirstdate

From m_units where Len (unitcoding) = 12 and (unitcoding like @ unitcoding)

--Insert the statistical date for the next half month(Example: 2004-1-16 ~ 2004-1-31)

Insert into # tblunitdateswap (unitcoding, startdate, enddate)

Select unitcoding, @ I _sseconddate, @ I _eseconddate

From m_units where Len (unitcoding) = 12 and (unitcoding like @ unitcoding)

--Insert the full month statistical date(Example: 2004-1-1 ~ 2004-1-31)

Insert into # tblunitdateswap (unitcoding, startdate, enddate)

Select unitcoding, @ I _sseconddate, @ I _eseconddate

From m_units where Len (unitcoding) = 12 and (unitcoding like @ unitcoding)

 

/*Execution time when the stored procedure is inserted(This function is disabled during formal execution.)*/

Insert into r_exectime (unitcoding, execname) values (@ unitcoding, 'start ')

 

Declare @ errorcode int

Declare @ I _unitcoding varchar (20)

Declare @ I _startdate varchar (10)

Declare @ I _enddate varchar (10)

 

/*Count the number of records inserted into the temporary table*/

Declare @ v_temptablecount int

Select @ v_temptablecount = count (*) from # tblunitdateswap

Declare @ I integer --Define a temporary Loop Variable

Select @ I = 1 --The initialization temporary variable is1

 

--Cycle each grass-roots unit(Unit code length:12)Save the statistical valueR_countCorresponding table

While (@ I <= @ v_temptablecount )--The execution cycle conditions are temporary variables.<=Temporary table records

Begin -- W01

Select @ I _unitcoding = unitcoding, @ I _startdate = startdate, @ I _enddate = enddate

From # tblunitdateswap

Where numberid = @ I

If not exists (select * From r_count1 where unitcoding = @ I _unitcoding and statdate = @ I _startdate)

Begin

Insert into r_count1

Exec ('dbo. sp_count1_auto_new '+ @ I _unitcoding +', '+ @ I _startdate +', '+ @ I _enddate)

End

 

Else --If a statistical record exists, delete the record and insert the latest statistical record again.(This function can be blocked to improve efficiency.)

Begin

Delete from r_count1 where unitcoding = @ I _unitcoding and statdate = @ I _startdate

Insert into r_count1

Exec ('dbo. sp_count1_auto '+ @ I _unitcoding +', '+ @ I _startdate +', '+ @ I _enddate)

End

--Get error code value

Select @ errorcode = @ Error

/***Temporary loop variable auto-Increment1 */

Select @ I = @ I + 1

End-- W01

 

/*Execution time when the stored procedure is inserted(This function is disabled during formal execution.)*/

Insert into r_exectime (unitcoding, execname) values (@ unitcoding, 'end ')

/*

--Number of debugging execution cycles

Declare @ JJ varchar (10)

Select @ JJ = convert (varchar (10), @ I)

Print'Number of executions: '+ @ Jj

*/

 

/*Delete temporary table*/

Drop table # tblunitdateswap

 

If (@ errorcode = 0)

Begin

Commit transaction

End

Else

Begin

Rollback transaction

End

Return

3,Data Mining

CreateProcedure DBO. sp_count1_auto_new

(

@ Unitcoding varchar (20 ),

@ Startdate char (8 ),

@ EnddateChar (8)

)

As

Declare @ Length Integer

 

Declare @ Len integer

Select @ length = Len (@ unitcoding)

If @ length = 4 or @ length = 2

Begin

Select @ Len = 2

End

Else if @ length = 12

Begin

Select @ Len = 0

End

Else

Begin

Select @ Len = 3

End

Select @ unitcoding as unitcoding,

@ Startdate as statdate,

DBO. getfcount_inout (unitcoding, '000000', @ enddate, 99,99, 99,0, 99,99, 99,0 ),

DBO. getfcount_inout (unitcoding, @ startdate, @ enddate, 99,99, 99,0, 99,99, 99,0 ),

DBO. getfcount_inout (unitcoding, '000000', @ enddate, 19000101, 0 ),

DBO. getfcount_inout (unitcoding, '000000', @ enddate, 19000101 ),

DBO. getfcount_inout (unitcoding, '000000', @ enddate, 19000101 ),

DBO. getfcount_inout (unitcoding, '000000', @ enddate, 19000101 ),

DBO. getfcount_inout (unitcoding, '000000', @ enddate, 19000101 ),

DBO. getfcount_inout (unitcoding, '000000', @ enddate, 19000101 ),

DBO. getfcount_inout (unitcoding, '000000', @ enddate, 19000101 ),

DBO. getfcount_inout (unitcoding, '000000', @ enddate, 19000101 ),

DBO. getfcount_inout (unitcoding, '000000', @ enddate, 19000101 ),

DBO. getfcount_inout (unitcoding, '000000', @ enddate, 19000101 ),

DBO. getfcount_inout (unitcoding, '000000', @ enddate, 19000101 ),

DBO. getfcount_inout (unitcoding, '000000', @ enddate, 19000101 ),

DBO. getfcount_inout (unitcoding, '000000', @ enddate, 99,99, 99,1, 99,99, 99,0 ),

DBO. getfcount_inout (unitcoding, @ startdate, @ enddate, 99,99, 99,1, 99,99, 99,0 ),

DBO. getfcount_inout (unitcoding, '000000', @ enddate, 19000101, 0 ),

DBO. getfcount_inout (unitcoding, '000000', @ enddate, 19000101)

From m_units

Where unitcoding like @ unitcoding + '%' and Len (unitcoding) = Len (@ unitcoding) + @ Len

Return

4,Data analysis display

Select DBO. getunitname (left (unitcoding, @ v_len)'Organization Name',

Sum (field1 ),

Sum (field2 ),

Sum (field3 ),

Sum (field4 ),

Sum (field5 ),

Sum (field6 ),

Sum (field7 ),

Sum (field8 ),

Sum (field9 ),

Sum (field10 ),

Sum (field11 ),

Sum (field12 ),

Sum (field13 ),

Sum (field14 ),

Sum (field15)

From DBO. u_count1_report_new (@ unitcoding, @ startdate, @ enddate)

Group by left (unitcoding, @ v_len), DBO. getunitname (left (unitcoding, @ v_len ))

Order by left (unitcoding, @ v_len), DBO. getunitname (left (unitcoding, @ v_len ))

/* FunctionTable

Create Function DBO. u_count1_report_new

(

@ Unitcoding varchar (20 ),

@ Startdate char (8 ),

@ EnddateChar (8)

)

Returns @ r_count1 table

(

Unitcoding varchar (20 ),

-- Statdate varchar (10 ),

Field1 int not null,

Field2 int not null,

Field3 int not null,

Field4 int not null,

Field5 int not null,

Field6 int not null,

Field7 int not null,

Field8 int not null,

Field9 int not null,

Field10 int not null,

Field11 int not null,

Field12 int not null,

Field13 int not null,

Field14 int not null,

Field15 int not null,

Field16 int not null,

Field17 int not null,

Field18 int not null

)

As

Begin

Declare @ Length Integer

Declare @ v_len integer

Select @ length = Len (@ unitcoding)

Select @ v_len = 12

Insert @ r_count1

Select left (unitcoding, @ v_len) as unitcoding,

-- Statdate,

Max (field1) as field1,

Max (DBO. getonemonth1 (left (unitcoding, @ v_len), @ startdate, @ enddate, 0) as field2,

Max (field3) as field3,

Max (field4) as field4,

Max (field5) as field5,

Max (field6) as field6,

Max (field7) as field7,

Max (field8) as field8,

Max (field9) as field9,

Max (field10) as field10,

Max (field11) as field11,

Max (field12) as field12,

Max (field13) as field13,

Max (field14) as field14,

Max (field15) as field15,

Max (DBO. getonemonth1 (left (unitcoding, @ v_len), @ startdate, @ enddate, 1) as field16,

Max (field17) as field17,

Max (field18) as field18

From r_count1

Where statdate >=@ startdate and statdate <= @ enddate

And unitcoding like @ unitcoding + '%'

Group by left (unitcoding, @ v_len)

Order by left (unitcoding, @ v_len)

Return

End