insert into and update in same time

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 2008 Forums

Transact-SQL (2008)

insert into and update in same time

Author

Topic

voyager838
Yak Posting Veteran

90 Posts

Posted - 2011-06-28 : 03:12:19

Hi!

I have a maintable that i want copy data into from
two subtables.

The subtables contains


st1
datetime  value
1         3     
2         4
3         0
4         -1

and the other table contains


st2
datetime  value
2         5
3         5
4         -12
5         8

now i want my maintable would looks like


st2
datetime  st1     st2
1         3
2         4        5
3         0        5
4         -1      -12
5                 8

I know i have to do insert into, and perhaps update (?),
but how can i avoid it do it separately and
do the both operations in the same time ?
or does it need to ?
what is the best way to create this maintable?

all suggestions would be appreciated!

thanks

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-06-28 : 03:18:52

SQL Server 2008 has a new statement called MERGE to do that in one go.
See here for example: http://weblogs.sqlteam.com/peterl/archive/2007/09/20/Example-of-MERGE-in-SQL-Server-2008.aspx

No, you're never too old to Yak'n'Roll if you're too young to die.

voyager838
Yak Posting Veteran

90 Posts

Posted - 2011-06-28 : 03:48:59

It looks very interesting, thanks.

it cross my mind
would it be easy to do something like

update maintable
set st2 = A.value
select A.datetime, A.value from
st2 A join maintable B on A.datetime = B.datetime

and then

insert into maintable (datetime, st2)
select A.datetime, A.value from
st2 A join maintable B on A.datetime != B.datetime

or does it have to do something with locks and other issue
that leads to that merge is better ?

thanks

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2011-06-28 : 13:12:11

I have not tested this but you might try something like:

;WITH cte AS
(SELECT datetime FROM st1
UNION
SELECT datetime FROM st2);
SELECT ct3.datetime, st1.value as st1, st2.value as st2
FROM cte
LEFT JOIN st1 ON cte.datetime = st1.datetime
LEFT JOIN st2 ON cte.datetime = st2.datetime

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2011-06-28 : 17:10:17

You can use merge and full join.

Mirko

My blog: http://mirko-marovic-eng.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources