How can Roll back inserted data

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How can Roll back inserted data

Author

Topic

Vishal_sql
Posting Yak Master

102 Posts

Posted - 2012-10-25 : 03:41:21

Hi All,

Below is the sample code i was trying and my scenario is
how can the inserted data is rollbacked?

i used cursor for validation
(comaparion of task in @temp table with @task table)

In output,the 2nd row in @temp does not satisfy the taskname in @task table
so I want to rollback the 1st inserted data.

declare @pcode varchar(100)
declare @rnum int
declare @tname varchar(100)

declare @temp table
(
projectcode varchar(100),
revisionnum int,
taskname varchar(100)
)

declare @temp2 table
(
projectcode varchar(100),
revisionnum int,
taskname varchar(100)
)

insert @temp
select 'P1',1,'CM' union all
select 'P1',1,'M' union all
select 'P1',1,'MM'

declare @task table
(
taskid int,
taskname varchar(100),
projectcode varchar(100),
revisionnum int
)

insert @task
select 1,'CM','P1',1 UNION ALL
select 2,'PM','P1',1 UNION ALL
select 3,'MM','P1',1

select * from @temp
select * from @task

declare curname cursor
for select * from @temp

open curname

fetch next from curname into @pcode,@rnum,@tname

while @@fetch_status=0
begin
if exists(select 1 from @task where taskname=@tname and projectcode=@pcode and revisionnum=@rnum)
begin
begin tran t1
insert @temp2
select * from @temp where projectcode=@pcode and revisionnum=@rnum and taskname=@tname
end
else
begin
rollback
break;
end
fetch next from curname into @pcode,@rnum,@tname
end

close curname
deallocate curname

select * from @temp2

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-10-25 : 04:45:02

For what you have there

insert @temp2 select t1.projectcode, t1.projectcode, t1.taskname
from @temp t1
join @task t2
on t1.projectcode = t2.projectcode
and t1.taskname = t2.taskname
and t1.revisionnum = t2.revisionnum

No need for cursor or transaction
Better to not insert rather than rollback. With a temp table you would probably delete or mark as deleted if you wanted to do this.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Vishal_sql
Posting Yak Master

102 Posts

Posted - 2012-10-25 : 05:12:37

i want to get all the records in @temp only if all rows in @temp satisfy the condition of valid task in @task
else there should be no any row inserted in @temp2.

so as i used cursor for validating each row,
1st row is get inserted into @temp2
so i was thinking it could be rollbacked ?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-10-25 : 05:38:18

In that case

insert @temp2
select t1.projectcode, t1.projectcode, t1.taskname
from @temp t1
where not exists
(select *
from @temp
left join @task t2
on t1.projectcode = t2.projectcode
and t1.taskname = t2.taskname
and t1.revisionnum = t2.revisionnum
where t2.projectcode is null
)

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-10-25 : 05:38:48

Hi,

One thing you can do is:

while @@fetch_status=0
begin
if exists(select 1 from @task where taskname=@tname and projectcode=@pcode and revisionnum=@rnum)
begin
begin tran t1
insert @temp2
select * from @temp where projectcode=@pcode and revisionnum=@rnum and taskname=@tname
end
else
begin
rollback
delete from @temp2
break;
end
fetch next from curname into @pcode,@rnum,@tname
end

--
Chandu

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-10-25 : 05:47:50

Get rid of the transaction - it doesn't do anything.

Better to just insert if all rows fulfill the requirement as in my previous post.
Why insert just to delete later? (And why have a cursor?)

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-10-25 : 05:52:19

As of now i provided temporary solution for that ...

I think he is doing some R&D....

--
Chandu

Vishal_sql
Posting Yak Master

102 Posts

Posted - 2012-10-25 : 06:30:03

@temp
projectcode revisionnum taskname
P1 1 CM
p1 1 PM
p1 1 MM

@task
taskid projectcode revisionnum taskname
1 P1 1 CM
2 p1 1 PM
3 p1 1 MM

but nigel your query doesnt fills the data fo above condition
plz have a look if i am not right.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-10-25 : 07:31:19

It should - there are a couple of typos which I guess you found - maybe you corrected them incorrectly :).

insert @temp2 (projectcode, revisionnum, taskname)
select projectcode, revisionnum, taskname
from @temp
where not exists
(select *
from @temp t1
left join @task t2
on t1.projectcode = t2.projectcode
and t1.taskname = t2.taskname
and t1.revisionnum = t2.revisionnum
where t2.projectcode is null
)

I'm also guessing that you want it per project rather than for the whole table? If so make the not exists bit a correlated subquery.
Don't try to overcomplicate it - it's a pretty simple requirement.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Vishal_sql
Posting Yak Master

102 Posts

Posted - 2012-10-25 : 10:27:30

Thanks Nigel.
Its great solution over the cursor.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources