update and insert in single statement in sqlserver

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

General SQL Server Forums

New to SQL Server Programming

update and insert in single statement in sqlserver

Author

Topic

subhaoviya
Posting Yak Master

135 Posts

Posted - 2011-01-18 : 05:19:02

Hi friends,

I am having two query in my SP.example shown as below.

Update t1
set myfield=t2.expectedfield
where t1.datestamp=t2.datestamp

Insert t1(myfield)
select
expectedfield from t2
where expectedfield not in(select myfield from t1)

please help me to make that both insert and update queries in single query and also if possible give me idea to optimise those two.

i am currently using sql2005.

thanks in advance
subha

Devart
Posting Yak Master

102 Posts

Posted - 2011-01-18 : 06:02:50

For example:

UPDATE:

UPDATE t1
SET myfield=t2.expectedfield
FROM t1 INNER JOIN t2 ON t1.datestamp=t2.datestamp

INSERT:

INSERT INTO t1(myfield)
SELECT t2.expectedfield
FROM t2 LEFT OUTER JOIN t1 ON t2.expectedfield=t1.myfield
WHERE t1.myfield IS NULL

Devart,
SQL Server Tools:
dbForge Data Studio
dbForge Schema Compare
dbForge Data Compare
dbForge SQL Complete

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources