Update multiple records in table using Stored Procedure in SQL Server

Last Reply 3 months ago By pandeyism

Posted 3 months ago

Hi Team,

I have 2 table - 1.Main Table and 2.History Table.

Main Table contains multiple columns with two date columns like -- End date and Review Date ( where Review date is less 5days from End date)

I want to insert data from Main table to History table when current date is between End date and Review Date then Update the End date to current date.

Please help me to do this operation with stored procedure.

Posted 3 months ago

Hi Waghmare,

Refer below sample query.

SQL

CREATE TABLE MainTable(ID INT, EndDate DATETIME, ReviewDate DATETIME)
INSERT INTO MainTable VALUES(1,'2019-06-15','2019-06-18')
INSERT INTO MainTable VALUES(2,'2019-06-19','2019-06-25')
INSERT INTO MainTable VALUES(3,'2019-06-10','2019-06-14')
INSERT INTO MainTable VALUES(4,'2019-06-05','2019-06-09')
INSERT INTO MainTable VALUES(5,'2019-07-01','2019-07-09')

CREATE TABLE #HistoryTable(ID INT, EndDate DATETIME, ReviewDate DATETIME)

CREATE PROCEDURE InsertUpdateTable
AS
BEGIN
DECLARE @CurrentDate DATETIME
SET @CurrentDate = (SELECT GETDATE())
	IF EXISTS(SELECT * FROM MainTable WHERE  @CurrentDate BETWEEN EndDate AND ReviewDate)
	BEGIN
		INSERT INTO HistoryTable(Id,EndDate,ReviewDate)
		SELECT * FROM MainTable WHERE  @CurrentDate BETWEEN EndDate AND ReviewDate
		UPDATE MainTable SET EndDate = @CurrentDate	
		SELECT * FROM MainTable
		SELECT * FROM HistoryTable
	END
END