How to create procedure for this problem [closed]

Question

I want to create a procedure to send data from one table (abc) to another table (xyz) and that data is moved to second table(xyz) only when that data is deleted from 1st table (abc)...

insert into copydelete (usernm  ,pas ,scholor)
  select username,pasword,scholorship from administraor where username= 'sunil' ;

delete from administraor  where username='sunil' ;

Answer 1

You could use the OUTPUT clause. Here's a simple demo:

USE tempdb
GO

IF OBJECT_ID('copydelete') IS NOT NULL DROP TABLE copydelete
CREATE TABLE copydelete
(
    usernm  VARCHAR(20),
    pas VARCHAR(20),
    scholor BIT,

    dateAdded DATETIME DEFAULT GETDATE(),
    addedBy VARCHAR(50) DEFAULT SUSER_NAME()
)
GO

IF OBJECT_ID('administraor') IS NOT NULL DROP TABLE administraor
CREATE TABLE administraor
(
    username VARCHAR(20),
    pasword VARCHAR(20),
    scholorship BIT
)
GO

INSERT INTO dbo.administraor VALUES 
    ( 'sunil', 'a', 1 ),
    ( 'wBob', 'b', 0 )
GO

SELECT 'before' s, * FROM dbo.administraor

INSERT INTO dbo.copydelete ( usernm, pas, scholor )
SELECT username, pasword, scholorship
FROM
    (
    DELETE dbo.administraor
    OUTPUT deleted.username, deleted.pasword, deleted.scholorship 
    WHERE username = 'sunil'
    ) x


SELECT 'after' s, * FROM dbo.administraor
SELECT 'after' s, * FROM dbo.copydelete