How to get rid of cursor? - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

How to get rid of cursor?

Author

Topic

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2010-04-18 : 03:06:03

I've table and rows as follow,

declare @t1 table
(idx int identity(1,1), TID varchar(20), SeatN varchar(5), Posi varchar(20), selldte datetime);

insert into @t1 values ('1989','5A','100','20100318 6:40PM');
insert into @t1 values ('1989','5A','110','20100318 8:40PM');
insert into @t1 values ('2890','1B','100','20100317 7:40PM');
insert into @t1 values ('2341','1C','100','20100313 2:40PM');

My 2nd table as follows,

declare @tTicketPosi table
(idx int identity(1,1) primary key clustered, TID varchar(20), SeatN varchar(5),
Posi varchar(20), selldte datetime);
/*@tTicketPosi(TID,SeatN) is unique*/

What I'm trying to do is
1. Get value from @t1 order by selldte, then insert into @tTicketPosi
2. The rules is if record not exist in @tTicketPosi then insert, else update

As a result, below is my total solutions

declare @t1 table
(idx int identity(1,1), TID varchar(20), SeatN varchar(5), Posi varchar(20), selldte datetime);

insert into @t1 values ('1989','5A','100','20100318 6:40PM');
insert into @t1 values ('1989','5A','110','20100318 8:40PM');
insert into @t1 values ('2890','1B','100','20100317 7:40PM');
insert into @t1 values ('2341','1C','100','20100313 2:40PM');

DECLARE @TID VARCHAR(20) -- TID
DECLARE @SeatN VARCHAR(5) -- SeatN
DECLARE @Posi VARCHAR(20) -- Posi
DECLARE @selldte datetime -- selldte

declare @tTicketPosi table
(idx int identity(1,1) primary key clustered, TID varchar(20), SeatN varchar(5),
Posi varchar(20), selldte datetime);

DECLARE db_cursor CURSOR FOR
select TID,SeatN,Posi,selldte from @t1 order by selldte;

OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @TID,@SeatN,@Posi,@selldte

WHILE @@FETCH_STATUS = 0   
BEGIN
        if not exists (select TID,SeatN from @tTicketPosi where TID=@TID and SeatN=@SeatN)
        begin
                print 'insert'
                insert into @tTicketPosi values(@TID,@SeatN,@Posi,@selldte);
        end
        else
        begin
                print 'update'
                update @tTicketPosi
                Set Posi=@Posi
                where TID=@TID and SeatN=@SeatN;
        end
FETCH NEXT FROM db_cursor INTO @TID,@SeatN,@Posi,@selldte
END

select TID,SeatN,Posi from @tTicketPosi;

CLOSE db_cursor   
DEALLOCATE db_cursor

I heard, cursors are the SLOWEST way to access data inside SQL Server.

How to customized my T-SQL above without using a cursor? Looking for help

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-04-18 : 03:20:01

[code]
insert into @tTicketPosi
select TID, SeatN, Posi, selldte
from @t1 t
where not exists
(
select *
from @tTicketPosi x
where x.TID = t.TID
and x.SeatN = t.SeatN
)
[/code]

KH
[spoiler]Time is always against us[/spoiler]

waterduck
Aged Yak Warrior

982 Posts

Posted - 2010-04-18 : 05:57:41

Pai seh...my sifu is busy...

merge into @tTicketPosi tTicketPosi
using (select * from @t1)t1 on t1.TID=tTicketPosi.TID and t1.SeatN=tTicketPosi.SeatN
when matched then update
set tTicketPosi.Posi = t1.Posi
when not matched then insert(TID, SeatN, Posi, selldte) values(t1.TID, t1.SeatN, t1.Posi, t1.selldte);

this sql script is only available for sql server 2008

Hope can help...but advise to wait pros with confirmation...

waterduck
Aged Yak Warrior

982 Posts

Posted - 2010-04-18 : 06:15:32

for sql server 2005 you can use

quote:

insert into @tTicketPosi
select TID, SeatN, Posi, selldte
from @t1 t
where not exists
(
select *
from @tTicketPosi x
where x.TID = t.TID
and x.SeatN = t.SeatN
)
khtan

plus
update @tTicketPosi set tTicketPosi.Posi = t1.posi
from @tTicketPosi tTicketPosi join @t1 t1 on tTicketPosi.TID=t1.TID and tTicketPosi.SeatN=t1.SeatN

Hope can help...but advise to wait pros with confirmation...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-04-18 : 07:11:27

thanks wd