September 21, 2010 at 6:42 pm
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 .....
September 21, 2010 at 7:31 pm
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.
September 21, 2010 at 7:43 pm
I'm Sorry I was really in a Hurry .so i did not look at it Properly
September 21, 2010 at 8:14 pm
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
September 23, 2010 at 7:27 am
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