Deleted Values Insert into a new tables

  • Hi All,

    I have Database DB and Table CB ..CB has 3 dependent Tables OB ,CP,CI

    So My Requirement is ..

    delete From CB

    where ID_NUM not Like '99%'

    But here delete will not work because of FK .. so i need to delete records from OB CP CI Tables first ...and I need to store all the Deleted Records of all the 4 Tables (CB,OB,CP,CI) in a seperate Table

    May be all 4 deleted values in a Table

    or

    each table deleted records in each Seperate newly created table..

    all i Know to go each child table and delete but ....It may be time Running Script ...

    Or

    Individuall for Each delete and Store in new table

    Can any one Help me on This .....

  • Please click on the first link in my signaure block and learn how to present data in a forum so that others may supply a tested answer.

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • I'm Sorry I was really in a Hurry .so i did not look at it Properly

  • You can use the OUTPUT clause with DELETE to save the deleted rows, like this. (You can use #temp tables or even permanent tables instead of the table variables in the example.

    declare @TableToDelete table (id int identity(1,1) primary key, data varchar(100))

    declare @TableToSave table (id int, data varchar(100))

    insert into @TableToDelete (data)

    values ('Apples'),('Oranges'),('Bananas'),('Chicken'),('Cantaloupes'),('Eggplant'),('Steak')

    select '@TableToDelete',* from @TableToDelete

    delete @TableToDelete

    output deleted.id,deleted.data into @TableToSave

    where data in ('Steak','Chicken')

    select '@TableToSave',* from @TableToSave

    select '@TableToDelete',* from @TableToDelete

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • nari.koud (9/21/2010)


    Hi All,

    I have Database DB and Table CB ..CB has 3 dependent Tables OB ,CP,CI

    So My Requirement is ..

    delete From CB

    where ID_NUM not Like '99%'

    But here delete will not work because of FK .. so i need to delete records from OB CP CI Tables first ...and I need to store all the Deleted Records of all the 4 Tables (CB,OB,CP,CI) in a seperate Table

    May be all 4 deleted values in a Table

    or

    each table deleted records in each Seperate newly created table..

    all i Know to go each child table and delete but ....It may be time Running Script ...

    Or

    Individuall for Each delete and Store in new table

    Can any one Help me on This .....

    I have taken as all the 4 tables are having same columns if so this is fine or you can have seperate archive table for each main table ,if not

    (all 4 tables having different columns )you can create seperate table for each deleted items in that table.

    Look at this:

    /* Say this are your tables */

    declare @cb table (i int, j int)

    declare @OB table (i int, j int)

    declare @cp table (i int, j int)

    declare @CI table (i int, j int)

    declare @data1 table (i int, j int,k int)

    /*The value K here shows that you are going to keep the relation details */

    /*

    1 indicates CB

    2 indicates OB

    3 indicates CI

    4 indicates CI

    */

    /*Conside this is as your New table */

    insert into @cb /*Primary table having FK so it is in top most position:1*/

    select 1,1

    union select 2,5

    unionselect 3,8

    unionselect 4,10

    insert into @OB/*Position:2 is child tables*/

    select 1,1

    union select 2,88

    unionselect 3,5

    unionselect 4,180

    insert into @cp/*Position:2 is child tables*/

    select 1,71

    union select 2,85

    unionselect 3,87

    unionselect 4,180

    insert into @CI/*Position:2 is child tables*/

    select 1,17

    union select 2,56

    unionselect 3,88

    unionselect 4,180

    Delete @cb

    OUTPUT DELETED.i,DELETED.j,1/*1 indicates CB table datas*/

    INTO @data1

    WHERE i BETWEEN 1 and 3

    Delete @OB

    OUTPUT DELETED.i,DELETED.j,2/*2 indicates OB table datas*/

    INTO @data1

    WHERE i BETWEEN 1 and 3

    Delete @cp

    OUTPUT DELETED.i,DELETED.j,3/*3 indicates CP table datas*/

    INTO @data1

    WHERE i BETWEEN 1 and 3

    Delete @CI

    OUTPUT DELETED.i,DELETED.j,4/*4 indicates CI table datas*/

    INTO @data1

    WHERE i BETWEEN 1 and 3

    select * from @data1

    Thanks

    Parthi

    Thanks
    Parthi

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply