Update t1 from t2 - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Update t1 from t2

Author

Topic

caisys
Starting Member

16 Posts

Posted - 2007-12-11 : 08:11:54

Hi,
I have two tables t1 and t2
t1 contains employee data for example
userid, firstname, lastname, info1, info2

t2 is a temp table i create from an excel file with some modification and contains
userid,firstname,lastname,info1

What i want to do is update table1 from table2 in a way that if the userid id in t2 exists in t1 then info1 field is copied, if the userid does not exist then a new entry in t1 is made.

Any ideas?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-12-11 : 08:14:01

http://weblogs.sqlteam.com/mladenp/archive/2007/07/30/60273.aspx

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out

caisys
Starting Member

16 Posts

Posted - 2007-12-11 : 08:40:49

Thank you very much, what is the best way to find existing answers instead of posting already answered questions and wasting others' times?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-12-11 : 08:43:29

Best way is to always check this thread to see if it answered there:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210

If not, use search facility provided to find similar questions (and answers).

If still no luck, then post your question.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-11 : 08:44:56

quote:
Originally posted by caisys

Thank you very much, what is the best way to find existing answers instead of posting already answered questions and wasting others' times?

Do google search

Madhivanan

Failing to plan is Planning to fail

caisys
Starting Member

16 Posts

Posted - 2007-12-11 : 08:52:30

I usually do google but some times it is not easy to express what I want to do in a way that will generate relevant results.

Back to the same subject, the example shows how to do it for a single row which is great, how do i do it for every entry in t2 ????

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-11 : 09:16:28

[code]-- Step one, UPDATE existing records
UPDATE x
SET x.Info = y.Info
FROM TargetTable AS x
INNER JOIN SourceTable AS y ON y.PkCol = x.PkCol

-- Step two, INSERT not present records
INSERT TargetTable
(
PkCol,
Info
)
SELECT s.PkCol,
s.Info
FROM SourceTable AS s
WHERE NOT EXISTS (SELECT * FROM TargetTable AS d WHERE d.PkCol = s.PkCol)[/code]

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources