Hello Everyone,
Please help to understand how i can write the store prodecure which will do following operations:-
suppose there are two tables named as table1 and table2 , the table 1 containting the old records but table2 containing fresh updated records. table2 is the replica of the table1 means having the same table staructure as table 1 having .
1) Now what we have to do is to compare the bothe records and list down all the records which exist in the table1(OLD TABLE) but not in table2(Updated table) .
2) once found the list of the records which are in the old table but not in new table, we have to set the status of those records as closed(which are not exist in the new table) .
Ramachandran MPosted Dec 5, 2019, 2:01 AM
DECLARE OldTableCursor CURSOR FOR
SELECT ID FROM OLDTABLE WHERE ID NOT INT (select NewTableID from NewTable)
OPEN OldTableCursor
FETCH NEXT FROM OldTableCursor
INTO @ID
WHILE @@FETCH_STATUS = 0
BEGIN
update oldTable set STATUS='Closed' where ID = @ID
FETCH NEXT FROM OldTableCursor
INTO @ID
END
CLOSE OldTableCursor;
DEALLOCATE OldTableCursor;
Ritendra MallPosted Dec 5, 2019, 3:46 AM
Ramachandran MPosted Dec 5, 2019, 3:19 AM
Ramachandran MPosted Dec 5, 2019, 1:56 AM
Ritendra MallPosted Dec 5, 2019, 1:47 AM
Ramachandran MPosted Dec 5, 2019, 1:42 AM
Ritendra MallPosted Dec 5, 2019, 1:36 AM
Ramachandran MPosted Dec 5, 2019, 1:30 AM
(
SELECT CTE.ID FROM (
SELECT ID FROM OLDTABLE
EXCEPT
SELECT ID FROM NETABLE
)AS CTE
)
Ramachandran MPosted Dec 5, 2019, 1:10 AM
if you are using triggers then execute below coding
Ritendra MallPosted Dec 5, 2019, 1:02 AM
Ritendra MallPosted Dec 5, 2019, 12:57 AM
@Ramachandran M
Ramachandran MPosted Dec 4, 2019, 11:17 PM
AS
BEGIN
--SELECT RECORDS TO VALIDATE
-- TABLE1 OLD TABLE AND TABLE2 NEW TABLE
SELECT * FROM TABLE1 WHERE ID NOT IN (SELECT ID FROM TABLE2);
UPDATE TABLE1 SET STATUS= CASE STATUS WHEN 'NEW' THEN 'CLOSED' ELSE STATUS END WHERE ID NOT IN (SELECT ID FROM TABLE2);
END
Ramachandran MPosted Dec 4, 2019, 4:26 AM