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

Question

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...

Sourabh Choubey · Answer

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 Choubey · Answer

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

Manas Mohapatra · Answer

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/

sudipta sanyal · Answer

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 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

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

Answers (4)