April 17, 2010 at 8:41 pm
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);
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
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?
April 18, 2010 at 3:12 am
Step one would be to find the relevant rows in @t1. Those would be the latest selldte per TID. To find those, you can use a CTE (see BOL for details).
;WITH cte AS
(
SELECT *,
ROW_NUMBER() OVER(PARTITION BY TID ORDER BY selldte DESC) AS row
FROM @t1
)
SELECT *
FROM cte
WHERE row = 1
Now you can use the results to do an update to all rows using an inner join.
To insert new rows you can either use that cte fom above again or you'd have to store the result in an intermediate table before doing update and insert.
Give it a try and get back here if you get stuck.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply