SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Update Table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

poser
Posting Yak Master

124 Posts

Posted - 11/12/2010 :  15:44:10  Show Profile  Reply with Quote
Hello, I am trying to update a table....
I want to insert into the EMP table from both the Name and ID table.
EMPID is PK for all....
But the EMP table already has data in it and does not allow duplicates.

I have a Emp table I want update from two other tables:

Emp table Has columns: EMPID, LName, FName, ObjID, KID

Name table has columns: EMPID, LName, Fname

ID table has columns: EMPID, ObjID, KID

Any easy way to do this?

Any and all help would be greatly appreciated....

Thanks...SQL Team is the best!
R/P


tkizer
Almighty SQL Goddess

USA
37296 Posts

Posted - 11/12/2010 :  16:32:53  Show Profile  Visit tkizer's Homepage  Reply with Quote
Please show us a data example.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

poser
Posting Yak Master

124 Posts

Posted - 11/12/2010 :  16:44:10  Show Profile  Reply with Quote
Emp table:
EmpID LName FName ObjID KID
1 Smith Jeff 23 77
2 Craig Bob 25 99

Name Table:
EmpID LName FName
1 Smith Jeff
2 Craig Bob
8 Welk Larry
14 Johns Jim

ID Table:
EmpID ObjID KID
1 23 77
2 25 99
8 27 22
14 44 24


This is the result I want in the Emp table:

EmpID LName FName ObjID KID
1 Smith Jeff 23 77
2 Craig Bob 25 99
8 Welk Larry 27 22
14 Johns Jim 44 24

Thanks for taking the time to look at this...
R/P
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37296 Posts

Posted - 11/12/2010 :  16:53:59  Show Profile  Visit tkizer's Homepage  Reply with Quote
INSERT INTO Emp (EmpID, LName, FName, ObjID, KID)
SELECT i.EmpID, n.LName, n.FName, i.ObjID, i.KID
FROM ID i
JOIN Name n
ON i.EmpID = n.EmpID
WHERE NOT EXISTS (SELECT * FROM Emp e WHERE i.EmpId = e.EmpId)

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37296 Posts

Posted - 11/12/2010 :  16:55:14  Show Profile  Visit tkizer's Homepage  Reply with Quote
Or using an OUTER JOIN:

INSERT INTO Emp (EmpID, LName, FName, ObjID, KID)
SELECT i.EmpID, n.LName, n.FName, i.ObjID, i.KID
FROM ID i
JOIN Name n
ON i.EmpID = n.EmpID
LEFT JOIN Emp e
ON i.EmpId = e.EmpId
WHERE e.EmpID IS NULL

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

poser
Posting Yak Master

124 Posts

Posted - 11/12/2010 :  19:13:10  Show Profile  Reply with Quote
Thanks Tara for all your help..
R/P
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37296 Posts

Posted - 11/12/2010 :  19:46:42  Show Profile  Visit tkizer's Homepage  Reply with Quote
You're welcome, glad to help.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000