DELETE one row from one table and insert that row into ANOTHER table


In this article, we will look into the procedure to Delete one row from one table and insert that row into another table.

Before writing this article I want to say few words to all of you.
First of all my sincere Thanks to my friend (CHANDER SHARMA), he motivated me a lot. Because of his motivation today am here to write this small article. This is my Debut article. So let’s proceed with this question.

DELETE one row from one table and insert that row into ANOTHER table

Description :-

Method Used – By using TRIGGER

We can achieve this requirement with two tables, why because we can delete one row in one table and insert that same row into another table. So we can take tables both with same structure; if we are not taking the tables with same structure we can’t full fill this requirement. So let’s look this requirement into action with very small and easy script.
Now am taking two tables with same structure, one is TEST1 and second is TEST2.

–Creating table TEST1
Create table test1
id int,
name varchar(10)

–Read the table TEST 1
Select * from TEST1

Read Table TEST 1
–Create another table TEST2 with same structure of TEST 1 without using CREATE command

Select * into test2
from test1
where 1=2

–See the table TEST2 is it created with same structure of TEST1 or not

Select * from test2

Read Table TEST 2

–Inserting few rows in table TEST1

Insert into test1
select 1,’Avinash’
union all
select 2,’Chander’
Union all
select 3,’Sharma’
Union all
select 4,’Pinal’
union all
select 5,’dave’
union all
select 6,’Sai’
union all
select 7,’Vinod’
union all
select 8,’Balmukund’

–Read data from table TEST1

Select * from test1

Inserted values in Table TEST 1

–Create trigger for our required operation

Create trigger delete_trigger
on test1–table name
for delete
–Declaring variables
declare @id int,
@name varchar(10)

–Read the data from DELETED MAGIC TABLE
select @id=id,@name=name from deleted

–Inserting deleted data into table TEST2
insert into test2 values (@id,@name)

–Now delete one row in Table TEST1

Delete from Test1
where id=1

–Read table TEST 2 now the deleted data is inserted in this table or not

Select * from TEST2

Value deleted from Table TEST1 inserted to Table TEST 2

1. Here I created two tables (TEST1, TEST2) for our requirement with same structure. After that I inserted few rows into first table TEST1.
2. Later, I created trigger on table TEST1.
3. After creation of trigger I deleted one row in table TEST1, that deleted row is then inserted into the table 2 that is TEST 2.
Hope you all enjoy this article. Your comments and queries are highly appreciated.
Heart full thanks to CHANDER SHARMA – Founder of PhpRinG Tutorials

VN:F [1.9.22_1171]
Your Feedback hels to Improve
Rating: 9.1/10 (34 votes cast)
VN:F [1.9.22_1171]
Rating: +29 (from 31 votes)
DELETE one row from one table and insert that row into ANOTHER table, 9.1 out of 10 based on 34 ratings


  1. Hi Avinash,
    Congratulations for your first post….u have a long way 2 go…..keep writing such good posts which will be helpful 2 every learner.
    Thank you so much for such nice words , am also a learner like you….ur words motivate me even more!!! 🙂

  2. nice one…
    I have a doubt…
    I have a table, in that somewhat n rows are there… then i need to split that table total n rows into 3 different sizes. That splitted 3 different rows into 3 different new tables.. Is it possible to achieve….
    Pls help to me solve this….

    • Thank u so much for visiting this site i hope you found this article as usefull,

      coming to your doubt ,
      its very interesting to me ,me to dont know about this .i learnt a new thing because of you
      great thanks.

      just follow the below script ,i hope this will clear your doubt

      –creating table test1
      create table #test1
      id int,
      name varchar(20)

      –insert few rows in the above table
      insert into #test1 values (1,’chander sharma’),(2,’avinash reddy’),(3,’saikrishna’),(4,’pinal dave’),(5,’vinod kumar’),(6,’balmukund’)

      –read the data from above table
      select * from #test1

      /* we have 6 rows in our table i want to divide those 6 rows into two groups by using NTILE() function,
      i hope u knew this function if u dnt know about this please see the functioning of this in site*/

      –dividing #test1 table data into 2 parts by using NTILE() function
      select * from (select id,name,ntile(2)over(order by id asc) as partitionnumber from #test1) as k

      /*so by using above query we can divide #test1 data into 2 groups,so our aim is not this we want to divide total
      data into some groups here i divided it into 2 groups,after that we want to move group one data into one table and group 2 data into another table
      so for that operation i can quickly create the following tables with same structure */

      –creating table test2
      create table #test2
      id int,
      name varchar(20),
      partitionnumber bigint

      –creating another table #test3 without using create command
      select * into #test3
      from #test2
      where 1=2

      –read the two tables #test2 and #test3

      select * from #test2

      select * from #test3

      /*so after reading above two tables we know that these two tables have same structure with out data
      lets insert data ,i mean group 1 data into #test2 table and group 2 data into #test3 table*/

      insert into #test2
      select,,k.partitionnumber from (select id,name,ntile(2) over( order by id asc ) as partitionnumber from #test1) as k
      where partitionnumber=1

      –execute above query lets see the partition 1 data is inserted or not
      select * from #test2
      –yup it inserted sucessfully then again write script for inserting partition 2 that is group 2 data into #test3

      insert into #test3
      select,,k.partitionnumber from (select id,name,ntile(2) over( order by id asc ) as partitionnumber from #test1) as k
      where partitionnumber=2

      –read the data from table #test3
      select * from #test3

      –finally call the two tables once
      select * from #test2
      select * from #test3
      /*finally i hope your requirement is fulfilled,for your quick practice just copy the above script execute as useval without doing anymodification
      once your practice is over please let me know your feedback.

      its very interesting question,indeed me to dont know it because of you i learnt something new for this day.thank u so much to you.

  3. Hi Avinash,

    Can we do it in different way like this…

    delete from Test_Data_1
    output deleted.[Name], deleted.Age, deleted.Sex into test_data (Name, Age, Sex)
    where Age = 27

Comments are closed.