If record found, then update else insert

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)

If record found, then update else insert

Author

Topic

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2010-04-15 : 13:51:52

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
----------------------------------------------------------
4	2341	1C	100	2010-03-13 14:40:00.000
3	2890	1B	100	2010-03-17 19:40:00.000
1	1989	5A	100	2010-03-18 18:40:00.000
2	1989	5A	110	2010-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 found in #tPosi then update using

update #tPosi set Posi=Posi where TID=TID and SeatN=SeatN

3. If record not found in #tPosi then insert using

insert into #tPosi values(TID,SeatN,Posi);

Really need help.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2010-04-15 : 15:16:17

you can run 2 separate statements - an update by joining the two tables and another insert with a not exists condition.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2010-04-15 : 16:46:33

USE MERGE statement:
I also assumed you want the latest date in #tPosi if there is a duplicated TID and SeatN.


MERGE #tPosi AS target
USING (SELECT TID, SeatN, Posi from (SELECT TID, SeatN, Posi, row_number() over(partition by TID, SeatN order by SellDte desc) as rnk from @t1)t where rnk = 1) as source (TID, SeatN, Posi)
ON (target.TID = source.TID and target.SeatN = source.SeatN)
WHEN MATCHED THEN
UPDATE SET Posi = source.Posi
WHEN NOT MATCHED THEN
insert (TID, SeatN, Posi) values(TID,SeatN,Posi);

quote:

Originally posted by Delinda

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
----------------------------------------------------------
4	2341	1C	100	2010-03-13 14:40:00.000
3	2890	1B	100	2010-03-17 19:40:00.000
1	1989	5A	100	2010-03-18 18:40:00.000
2	1989	5A	110	2010-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 found in #tPosi then update using

update #tPosi set Posi=Posi where TID=TID and SeatN=SeatN

3. If record not found in #tPosi then insert using

insert into #tPosi values(TID,SeatN,Posi);

Really need help.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-04-15 : 18:37:16

see this

might help?

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=123495&SearchTerms=INSERT,based,on,three

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2010-04-15 : 23:22:07

quote:

Originally posted by hanbingl

USE MERGE statement:
I also assumed you want the latest date in #tPosi if there is a duplicated TID and SeatN.


MERGE #tPosi AS target
USING (SELECT TID, SeatN, Posi from (SELECT TID, SeatN, Posi, row_number() over(partition by TID, SeatN order by SellDte desc) as rnk from @t1)t where rnk = 1) as source (TID, SeatN, Posi)
ON (target.TID = source.TID and target.SeatN = source.SeatN)
WHEN MATCHED THEN
UPDATE SET Posi = source.Posi
WHEN NOT MATCHED THEN
insert (TID, SeatN, Posi) values(TID,SeatN,Posi);

quote:

Originally posted by Delinda

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
----------------------------------------------------------
4	2341	1C	100	2010-03-13 14:40:00.000
3	2890	1B	100	2010-03-17 19:40:00.000
1	1989	5A	100	2010-03-18 18:40:00.000
2	1989	5A	110	2010-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 found in #tPosi then update using

update #tPosi set Posi=Posi where TID=TID and SeatN=SeatN

3. If record not found in #tPosi then insert using

insert into #tPosi values(TID,SeatN,Posi);

Really need help.

Me using SQL Server 2005, i'm afraid there's no MERGE function

ColdCoffee
Starting Member

1 Post

Posted - 2010-04-16 : 07:08:00

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources