Skip to main content
W
Winston2
Inspiring
April 2, 2008
Question

Adding data to SQL 2000 table

  • April 2, 2008
  • 2 replies
  • 446 views
I have two SQL 2000 tables below.

T:Employees
EmpID
Fname
Lname
...

T:Security
SecurityID
Fname
Lname
Gnumb
...

I want to add "Gnumb" data to the Employee table. What is the smartest way to do this? I was thinking of querying the Employee table and for each record check to see if there was a Gnumber if not then query the Security table matching first and last name - if match, then update the Employee table with the Gnumb. But now I'm wondering if there is a better way. Also, no doubt some of the names will be mispelled between the two tables.Thank you.




This topic has been closed for replies.

2 replies

P
paross1
Participating Frequently
April 2, 2008
Why isn't Employee linked somehow to Security by EmpID instead of by name?

Phil
J
JR__Bob__Dobbs-qSBHQ2
Inspiring
April 2, 2008

A better solution might be to setup a relationship between the two tables, such as adding a foreign keyed EmpID column to the Security table, so that you don't have to duplicate data between them.


-- If you are unable to change your database schema this query might help you.
UPDATE E
SET E.Gnumb = S.Gnumber
FROM Employees AS E INNER JOIN Security AS S
ON ( E.Fname = S.Lname AND E.Lname = S.Lname )
WHERE S.Gnumber IS NOT NULL
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices