Select, Insert, Update Function

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

Select, Insert, Update Function

Author

Topic

sinjin67
Yak Posting Veteran

53 Posts

Posted - 2012-01-07 : 23:48:10

Good Day, I can use the following sample to achieve 99%
of what I need.

----------------------
SELECT ServiceID, RID
FROM SRVOPS.Service
WHERE RID = PET112
------------------------

How can the WHERE be adapted to look at the value of
of a specific field in table_a and match it to the
value of of the field with the same name in table_b,
without knowing the value already like in the above
sample

I have tried something like this and had no luck.

SELECT ServiceID, RID
FROM SRVOPS.Service
WHERE RID = RID

Once I have a match I want to update that record, else
if no match create a new record in table_b.

I cant put my finger on what I am missing as I have not
tried this approach before. Am I way off or does someone
have a better example/method they can share.

Any help would be great.
Thanx.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-08 : 02:17:41

I dunno your tableb's name so below stub make sure you replace tableB with actual table name and also Field with relavant field names


UPDATE b
SET b.Field= a.relatedfield,
...
FROM TableB b
INNER JOIN SRVOPS.Service a
ON a.RID = b.RID

INSERT TableB
SELECT columns
FROM SRVOPS.Service a
WHERE NOT EXISTS(SELECT 1 FROM TableB WHERE RID = a.RID)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sinjin67
Yak Posting Veteran

53 Posts

Posted - 2012-01-08 : 02:32:29

Ok, thanks for the pointer. I will give it a try and let you know.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-08 : 03:24:11

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2012-01-08 : 03:39:52

Could the INSERT be based on:


IF @@ROWCOUNT = 0
INSERT ...

rather than a NOT EXISTS ?

(Might be more efficient?)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-08 : 03:58:34

quote:
Originally posted by Kristen

Could the INSERT be based on:

IF @ROWCOUNT = 0
INSERT ...

rather than a NOT EXISTS ?

(Might be more efficient?)

hope you meant
@@ROWCOUNT

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2012-01-08 : 05:14:09

Yup, that's the one!

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-01-08 : 07:45:43

Maybe, but only in the case where you absolutely know that there's only a single row that's going to be either inserted or updated. As soon as you potentially have a set of rows where some will need updating and some inserting, then it needs the EXISTS construct (or a SQL 2008 MERGE)

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources