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

19
3449

Aim:-
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
as
–Declaring variables
declare @id int,
@name varchar(10)

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

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

–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

Summary:-
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
  • Aakash

    Hi Avinash,

    Congrats for your first post !!!
    Great Post.

    • Hai aakash,
      first of all thank u so much for viewing this article
      thanks a lot for ur kind of words with great pleasure…

  • souji

    just awesome. Keep going.

  • 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!!! 🙂

    • Thank u so much for your kind of words bro.this credit really goes to you only.because of you today am here .thanks for your motivation its memorable moments to me my sincere and heartfull thanks to u my friend in future also we both are together and write more and more article like this

      Thanks,
      Avinash reddy munnangi

      • Thanks Avinash,
        Learning is the best skill anybody can have in his/her life.
        And Be motivated and Enthusiastic for future posts !!!

        • yup your are right my friend thanks for your support .we can post one more article very soon

          thanks

  • DB DB

    Hi Avinash,

    My wishes for your first post. keep post . clearly explained without confusion. Simple but good .

  • Thank u so much for your support dinesh thanks for your kind of words

  • senzosh

    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 http://www.msdn.com 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.id,k.name,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.id,k.name,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.

  • “if we are not taking the tables with same structure we can’t full fill this requirement.”
    Why so?
    What’s the problem if you have less or more columns in Table “TEST2”

    • first of all thank u so much for your visiting

      yup your are right my friend sandeep mittal we can have more columns in table TEST2,i hope the extra columns are filled with null values .

  • sanjiv

    nice..

  • Ashish Kumar

    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

  • Satya

    its very helpful Avinash….