I want to write a stored procedure, everybody teaches me!

zhuang854174

I want to write a stored procedure, which is to periodically insert and delete data into the database. Can it be achieved using stored procedures? How to do it, let's give an example ~

fanfan1980

Regularly insert and delete data into the database

It is recommended to call PROC with JOB

我是杀人狂

It's better to use homework regularly ~

传说中的变态

It can be achieved with JOB.
Database Management New JOB1 sql: insert into tablename ...
New JOB2 sql: delete from tablename

dream88

Example of turning boss
IF EXISTS (SELECT * FROM msdb.dbo.sysjobs WHERE name = 'Enable pubs database')
EXEC msdb.dbo.sp_delete_job @ job_name = 'Enable pubs database'

--Define creation job
DECLARE @jobid uniqueidentifier
EXEC msdb.dbo.sp_add_job
@job_name = N 'Enable the pubs database',
@job_id = @jobid OUTPUT

-Define job steps
DECLARE @sql nvarchar (400), @ dbname sysname
SELECT @ dbname = N'master ', --Online or offline database can only be performed in the master database
@ sql = N'ALTER DATABASE pubs SET ONLINE '-use pubs database online (enable)
EXEC msdb.dbo.sp_add_jobstep
@job_id = @jobid,
@step_name = N 'Enable pubs database processing',
@subsystem = 'TSQL',
@ database_name = @ dbname,
@command = @sql

--Create scheduling (using several job scheduling templates specifically defined later)
EXEC msdb..sp_add_jobschedule
@job_id = @jobid,
@name = N 'Enable pubs database processing scheduling',
@ freq_type = 4,
@ freq_interval = 1,
@ freq_subday_type = 0x1,
@ freq_subday_interval = 1,
@active_start_time = 075000-every day at 07:50

--Add target server
DECLARE @servername sysname
SET @ servername = CONVERT (nvarchar (128), SERVERPROPERTY (N'ServerName '))
EXEC msdb.dbo.sp_add_jobserver
@job_id = @jobid,
@server_name = @servername

10118419

Use homework

zhuang854174

Still don't quite understand ~

wgfxman

You can also write a stored procedure, call him regularly in JOB