procedure to delete data from table when data in a row excee

Sourabh Choubey

9y872

I want to create a procedure to delete data from table when data in a row exceeds than 30thousand and at that tym make a backup ...Any help will be greatly appreciated...

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Sourabh Choubey9y ago
sudipta have you saw my question carefully...i want to called the sp in job and i want sp that delete rceords in a particular table when row in a table exceeds than 30k before deleting the past records it will create a backup of my databse
Sourabh Choubey9y ago
I know that job is required and i have to called the procedure in the job.i have written job
i only want the stored procedure can u please help me suppose that there is a table called notificationtemp and i want to records when it exceeds over 30 thousand rows and before deleting records it will create backup of my datatabase .Thanks
sudipta sanyal9y ago
Try This
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = N'test_data')
BEGIN
CREATE TABLE tempTest (ID INT IDENTITY(1,1),test_data VARCHAR(50))
END
DECLARE @id INT
SELECT @id = 1
WHILE @id >=1 AND @id <= 10
BEGIN
INSERT INTO tempTest (test_data) VALUES('sudipta_test' + convert(VARCHAR(5), @id))
SELECT @id = @id + 1
END
-----------------------------
DECLARE
@dynamicTableName VARCHAR(100) = ''
,@dynamicQuery NVARCHAR(MAX)
IF (SELECT COUNT(ID) FROM tempTest) > 9
BEGIN
SELECT @dynamicTableName = 'tempTest' + CONVERT(VARCHAR(8), GETDATE(), 112) + REPLACE(CONVERT(varchar, GETDATE(), 108), ':','')
SELECT @dynamicQuery = 'SELECT * INTO '+ @dynamicTableName +' FROM tempTest'
PRINT @dynamicQuery
EXECUTE (@dynamicQuery)
SELECT @dynamicQuery = 'SELECT * FROM ' + @dynamicTableName
EXECUTE (@dynamicQuery)
DELETE FROM tempTest
END
Manas Mohapatra9y ago
To accomplish your functionality, you create a job which runs interval basis(hr or day). It execute your SP and it checks whether data exceeds more 30K. if so then take back up and delete data. Top create a SP and execute through Job follow below link:

http://www.c-sharpcorner.com/UploadFile/raj1979/create-and-schedule-a-job-in-sql-server-2008/