April 15, 2010 at 9:34 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');
At this level, my record in @t1 in order by selldte as follow,
idx | TID | SeatN | Posi | SellDte
----------------------------------------------------------
423411C1002010-03-13 14:40:00.000
328901B1002010-03-17 19:40:00.000
119895A1002010-03-18 18:40:00.000
219895A1102010-03-18 20:40:00.000
Now, me create a temp table as follow,
create table #tPosi
(idx int identity(1,1) primary key clustered, TID varchar(20), SeatN varchar(5), Posi varchar(20));
alter table #tPosi add constraint tPosi01 unique(TID,SeatN);
My question is
1. I want to insert my record in @t1 into #tPosi
2. If record not found in #tPosi then insert using
insert into #tPosi values(TID,SeatN,Posi);
3. If record found in #tPosi then update using
update #tPosi set Posi=Posi where TID=TID and SeatN=SeatN
4. The most important thing is, we need to check the existence of rows in #tPosi first, then insert else update
5. The checking of existence rows must be in sequence based on @t1 in order by selldte as follow,
idx | TID | SeatN | Posi | SellDte
----------------------------------------------------------
423411C1002010-03-13 14:40:00.000 -- 1st check
328901B1002010-03-17 19:40:00.000 -- 2nd check
119895A1002010-03-18 18:40:00.000 -- 3rd check
219895A1102010-03-18 20:40:00.000 -- 4th check
How T-SQL look's like? really need help
April 16, 2010 at 12:05 am
Hey Delinda..
Here is the code for you.. this will update if an match is found and will insert if no match is found between the tables..
Tell us if this worked for u
--== FIRST LETS UPDATE THE DATA INTO THE SECOND TABLE
--== IF THE ROWS DOES EXIST
UPDATE T2
SET T2.Posi = T1.Posi
FROM
#tPosi T2
INNER JOIN
#T1 T1
ON
(
T1.SeatN = T2.SeatN
AND
T1.TID = T2.TID
)
WHERE
T1.selldte = (SELECT MAX(selldte)
FROM #T1
WHERE
TID = T1.TID AND
SeatN = T1.SeatN )
--== THEN LETS INSERT THE DATA INTO THE SECOND TABLE
--== IF THE ROWS DOES NOT EXIST
INSERT INTO #tPosi (TID, SeatN,Posi)
SELECT
T1.TID, T1.SeatN,T1.Posi
FROM
#T1 T1
LEFT JOIN
#tPosi T2
ON
(
T1.TID = T2.TID
AND
T1.SeatN = T2.SeatN
)
WHERE
(
T2.SeatN IS NULL
AND
T2.TID IS NULL
)
ORDER BY
T1.selldte
By the way, thank u so much for providing such a clear requirement... made life a lot more easier 🙂
Cheers!!
April 16, 2010 at 5:08 am
Me run as follow,
--== FIRST LETS UPDATE THE DATA INTO THE SECOND TABLE
--== IF THE ROWS DOES EXIST
UPDATE T2
SET T2.Posi = T1.Posi
FROM
#tPosi T2
INNER JOIN
@T1 T1
ON
(
T1.SeatN = T2.SeatN
AND
T1.TID = T2.TID
)
WHERE
T1.selldte = (SELECT MAX(selldte)
FROM @T1
WHERE
TID = T1.TID AND
SeatN = T1.SeatN )
--== THEN LETS INSERT THE DATA INTO THE SECOND TABLE
--== IF THE ROWS DOES NOT EXIST
INSERT INTO #tPosi (TID, SeatN,Posi)
SELECT
T1.TID, T1.SeatN,T1.Posi
FROM
@T1 T1
LEFT JOIN
#tPosi T2
ON
(
T1.TID = T2.TID
AND
T1.SeatN = T2.SeatN
)
WHERE
(
T2.SeatN IS NULL
AND
T2.TID IS NULL
)
ORDER BY
T1.selldte
It return error as follows,
Msg 2627, Level 14, State 1, Line 37
Violation of UNIQUE KEY constraint 'tPosi01'. Cannot insert duplicate key in object 'dbo.#tPosi'
April 16, 2010 at 5:23 am
miss.delinda (4/16/2010)
It return error as follows,Msg 2627, Level 14, State 1, Line 37
Violation of UNIQUE KEY constraint 'tPosi01'. Cannot insert duplicate key in object 'dbo.#tPosi'
Yes it will.. look at your sample data.. for the same TID and SeatN you are having 2 positions...
so first
1.u will have to remove that row (row with TID = 1989 and SeatN= 5A)
2.run the query i gave
3.insert that removed row into the source table
4.Run the query i gave..
Now, how much ever records u add to that source table, whenever u run my code, it will "update" the new tPos of the MAX(selldte) for the existing rows and inserts any new records..
Hope i made u clearer w.r.t the code.. please revert back for any further details...
Cheers!!
April 16, 2010 at 5:39 am
Hello
It would be better - much better - to identify the rows you want from @t1 then insert them into #tPosi in a single INSERT...FROM.
So "we need to check the existence of rows in #tPosi first" becomes "we need to perform some kind of aggregate of our data".
Probably the easiest way to do this, is to extend your sample data set for @t1 and mark the rows which will appear in #tPosi.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply