merging a source to a target that links two tables

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)

merging a source to a target that links two tables

Author

Topic

sarahmfr
Posting Yak Master

214 Posts

Posted - 2011-08-20 : 08:37:35

Hi,
I have data in a table say Source:orguserid,firstname,lastname
I want to merge it into a table say user:userid,firstname,lastname
that is linked to a table UserOrg:userid,orguserid,orginfo

So that I insert into table user any new record from source depending on orguserid

i.e. the unique identifier that determines whether the record exists or not is in another table that is linked to the table I want to insert new information to.

Appreciate your help

sarah

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-20 : 09:11:24

i think this is what you want


insert into user(firstname,lastname)
select s.firstname,s.lastname
from Source s
left join UserOrg uo
ON uo.orguserid = s.orguserid
where uo.orguserid IS NULL

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

sarahmfr
Posting Yak Master

214 Posts

Posted - 2011-08-20 : 09:28:53

No sorry this is not what i want
I have a table Temp (orguserid,firstname,lastname)
this table has all the names
I want to merge this information to a table user (userid,firstname,lastname)--this table does not have the orguserid that identifies whether to insert or update the orguserid is in another table userorg (userid,orguserid)

i.e. I want to insert into user table but only those whose orguserid do not exist in userorg table

Thanks

sarah

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-20 : 09:49:28

quote:
Originally posted by sarahmfr

No sorry this is not what i want
I have a table Temp (orguserid,firstname,lastname)
this table has all the names
I want to merge this information to a table user (userid,firstname,lastname)--this table does not have the orguserid that identifies whether to insert or update the orguserid is in another table userorg (userid,orguserid)

i.e. I want to insert into user table but only those whose orguserid do not exist in userorg table

Thanks

sarah

isnt this exactly what i've given in my insert?

for update, it will be like


update u
set u.firstname=s.firstname,
u.lastname=s.lastname
from Source s
inner join UserOrg uo
ON uo.orguserid = s.orguserid

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

sarahmfr
Posting Yak Master

214 Posts

Posted - 2011-08-21 : 13:42:43

Thanks a lot

sarah

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-22 : 01:33:41

welcome

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources