if exists update else insert - SQL Server Forums

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

if exists update else insert

Author

Topic

insanepaul
Posting Yak Master

178 Posts

Posted - 2010-01-06 : 05:56:21

Hi, I have 2 tables A and B. I want to update B where the id is the same as A and insert values where they differ.

A B
0 0
1 2
2
3

I'm thinking of left joining A and B but getting muddled as I can't work out how to do this...I'm thinking on the lines of:
IF EXISTS(SELECT Id, Name, ConnectionString FROM B WHERE Id = )

but still not sure if I'm on the right lines

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-06 : 05:58:57

do you mean this?


UPDATE b
SET b.field=a.field1,
b.Field2=a.field2,..
FROM B b
JOIN A a
ON a.ID=b.ID
WHERE a.Field1 <> b.field1
OR a.Field2<> b.Field2
...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-01-06 : 06:19:18

Are you using SQL Server 2008? Then have a look at the MERGE command.

N 56°04'39.26"
E 12°55'05.63"

insanepaul
Posting Yak Master

178 Posts

Posted - 2010-01-06 : 06:29:39

quote:

Originally posted by visakh16

do you mean this?


UPDATE b
SET b.field=a.field1,
b.Field2=a.field2,..
FROM B b
JOIN A a
ON a.ID=b.ID
WHERE a.Field1 <> b.field1
OR a.Field2<> b.Field2
...

Hi, Thanks for those amzingly fast responses. I have a couple of questions.

1. I'm updating where the id's match so "WHERE a.Field1 <> b.field1" I think should read
"WHERE a.Field1 = b.field1"

2. In the insert part I make a leftjoin then use your line
WHERE a.Field1 = b.field1

3.I think i've got the update and insert correct. Do I need the "if exists.... else... statement?

insanepaul
Posting Yak Master

178 Posts

Posted - 2010-01-06 : 06:30:33

quote:
Originally posted by Peso

Are you using SQL Server 2008? Then have a look at the MERGE command.

In my research I came across this merge in SQL 2008 but alas we don't have that yet

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-06 : 06:33:57

quote:
Originally posted by insanepaul

quote:
Originally posted by visakh16

do you mean this?

UPDATE b
SET b.field=a.field1,
b.Field2=a.field2,..
FROM B b
JOIN A a
ON a.ID=b.ID
WHERE a.Field1 <> b.field1
OR a.Field2<> b.Field2
...

Hi, Thanks for those amzingly fast responses. I have a couple of questions.

1. I'm updating where the id's match so "WHERE a.Field1 <> b.field1" I think should read
"WHERE a.Field1 = b.field1"

2. In the insert part I make a leftjoin then use your line
WHERE a.Field1 = b.field1

3.I think i've got the update and insert correct. Do I need the "if exists.... else... statement?

1. nope you need the update only when values in two tables differ
2.insert part? sorry posted query has only update no insert
3.sorry didnt understand that. do u mean u want both insert & update?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-06 : 06:36:44

i think what you're asking for is this


UPDATE b
SET b.field=a.field1,
b.Field2=a.field2,..
FROM B b
JOIN A a
ON a.ID=b.ID
WHERE a.Field1 <> b.field1
OR a.Field2<> b.Field2
...

INSERT INTO B (columns..)
SELECT a.columns..
FROM A a
LEFT JOIN B b
ON b.ID=a.ID
WHERE b.ID IS NULL

insanepaul
Posting Yak Master

178 Posts

Posted - 2010-01-06 : 06:45:31

[/quote]
1. nope you need the update only when values in two tables differ
2.insert part? sorry posted query has only update no insert
3.sorry didnt understand that. do u mean u want both insert & update?
[/quote]

Basically I've got to syncronise 2 tables from different databases when a user makes the request. I've populated a temp table in my proc with the values from Table A and need to sync this with Table B.

Using my example in the top of this thread the result to table B is having 0,1,2,3 where 0,2 is updated and 1,3 would be inserted.

I think I need an IF exists update else insert command but not sure how to write it.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-06 : 06:50:15

quote:
Originally posted by insanepaul

1. nope you need the update only when values in two tables differ
2.insert part? sorry posted query has only update no insert
3.sorry didnt understand that. do u mean u want both insert & update?
[/quote]

Basically I've got to syncronise 2 tables from different databases when a user makes the request. I've populated a temp table in my proc with the values from Table A and need to sync this with Table B.

Using my example in the top of this thread the result to table B is having 0,1,2,3 where 0,2 is updated and 1,3 would be inserted.

I think I need an IF exists update else insert command but not sure how to write it.
[/quote]
then wat you need is my last posted soln

insanepaul
Posting Yak Master

178 Posts

Posted - 2010-01-06 : 07:26:50

quote:

Originally posted by visakh16

i think what you're asking for is this


UPDATE b
SET b.field=a.field1,
b.Field2=a.field2,..
FROM B b
JOIN A a
ON a.ID=b.ID
WHERE a.Field1 <> b.field1
OR a.Field2<> b.Field2
...

INSERT INTO B (columns..)
SELECT a.columns..
FROM A a
LEFT JOIN B b
ON b.ID=a.ID
WHERE b.ID IS NULL

Thanks for that, just 1 thing I've altered. In the UPDATE section I don't need the WHERE clause because we already using the ON which would filter the results. Many Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-06 : 07:29:54

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources