Issues Populating Table - 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 2000 Forums

Transact-SQL (2000)

Issues Populating Table

Author

Topic

griffed
Starting Member

5 Posts

Posted - 2002-06-13 : 18:19:35

I believe that I am having issues with the logic that I am using to try and populate a table after comparing information in a different database.

Brief description of what I am trying to do:

I have 2 different data bases that contain list of employees. In the first data base I list each employees Manager and all ascociated info as columns in the data base. In the second data base I have assigned each employee a unique ID number. I am trying to update each employee record in the second data base to reflect that employee's manager unique ID in data base 2 after determining the employees manager from the employee's record in data base 1.

Here is how the data bases are designed:

DataBase1
EmployeeInfoTable
EmployeeName
EmployeeMgrName

DataBase2
EmployeeInfoTable
Employee_ID
EmployeeName
EmployeeMgr_ID

Here is what I tried:

use DataBase2
go
UPDATE dbo.EmployeeInfoTable
SET EmployeeMgr_ID = Employee_ID
FROM dbo.EmployeeInfoTable a
JOIN DataBase1..EmployeeInfoTable b
ON a.EmployeeName = b.EmployeeMgrName

The results:

The query ran fine but I did not get a manager ID for every employee that had a manger listed in data base 1. The result That I got was an apperntly random selection of employees that in data base 2 appear to be there own manager yet when I go and check in data base 1, the employee is not listed as his own manager.

izaltsman
A custom title

1139 Posts

Posted - 2002-06-14 : 09:16:38

Try something along these lines:


UPDATE tgt
SET 
    EmployeeMgr_ID = src.Mgr_Id
FROM
    Database2..EmployeeInfoTable as tgt
    INNER JOIN  
    (SELECT emp2.Employee_ID, man2.Empllyee_id as Mgr_Id
     FROM 
        Database2..EmployeeInfoTable emp2
        INNER JOIN Database1..EmployeeInfoTable emp1
            ON emp2.EmployeeName = emp1.EmployeeMgrName 
        INNER JOIN Database2..EmployeeInfoTable man2
            ON emp1.EmployeeMgrName = man2.EmployeeName
     ) as src
        ON tgt.Employee_id = src.Employee_id

Edited by - izaltsman on 06/14/2002 09:17:31

griffed
Starting Member

5 Posts

Posted - 2002-06-17 : 11:18:57

That worked Great. Thanks!

griffed
Starting Member

5 Posts

Posted - 2002-06-19 : 11:44:55

Another question, as I work on populating a data base from different data bases.

What process is taken to come up with a script like the above query?

The reason I am asking is that I am up against a similar situation. I would like to try and come up with the solution myself but I am at the point where I understand what was going on in the first query:

quote:
UPDATE tgt
SET
EmployeeMgr_ID = src.Mgr_Id
FROM
Database2..EmployeeInfoTable as tgt
INNER JOIN
(SELECT emp2.Employee_ID, man2.Empllyee_id as Mgr_Id
FROM
Database2..EmployeeInfoTable emp2
INNER JOIN Database1..EmployeeInfoTable emp1
ON emp2.EmployeeName = emp1.EmployeeMgrName
INNER JOIN Database2..EmployeeInfoTable man2
ON emp1.EmployeeMgrName = man2.EmployeeName
) as src
ON tgt.Employee_id = src.Employee_id

just not why the query was constructed as it was.

The project that I am working on is taking information from a variety of spread sheets that we are currently using to track a variety of IT projects for 32000 desktop PC users and 50000 PCs. I have ported all the spreadsheet info into SQL server 2000 into database1 and datbase2 is the normalized database. The tables that I am concerned with are set up like this:

databae1
table1
PCname
CustomerName

database2
table1
CustomerID
CustomerName

table2
PCname
CustomerID

What I am trying to populate is the CustomerID in database2.table2. How do I start to build this Query?

izaltsman
A custom title

1139 Posts

Posted - 2002-06-19 : 16:08:56

I am sure there are different ways to approach a problem like yours, but I'll try to show how I do it.

First of all, let's clarify some assumptions:
database1..table1 is completely populated
database2..table1 is completely populated
database2..table2 already contains all the PCNames you want to have there, but has NULLs/junk for
CustomerIDs

The problem: build a SQL statement to UPDATE CustomerID field in the database..table2, so that all PCNames are matched up to the appropriate customer.

So the problem states you'll need CustomerIds... Where can you get CustomerIDs from? You don't have a whole lot of options here -- CustomerID field is only populated in database2..table1. Therefore, you will definitely need that table. But in order to UPDATE database2..table2, you have to figure out a way to tie CustomerID to the PCName. Luckily for you, you have database1..table1 that contains PCNames, and CustomerNames. You can leverage CustomerName field to combine (join) the information from database1..table1 and database2..table1, thus associating PCNames and CustomerIds. Once you have a SELECT statement that
returns PCNames and CustomerIDs, updating database2..table2 becomes trivial (you simply join the table and the SELECT, based on the PCName field).

I hope this helps. If not -- let me know and I will post a ready-made solution for you... If you go over it, and then look over my comments again, I am sure it will start to make sense.

BTW,
Note that solution to this problem will have less joins than solution to the problem you posted in the beginning of this thread. An extra join in the solution to the first problem was needed to deal with the relationship between managers and their subordinates.

Edited by - izaltsman on 06/19/2002 16:09:42

griffed
Starting Member

5 Posts

Posted - 2002-06-24 : 12:54:31

So this is what I came up with. And It seemed to work on. out of 25000 PCnames I was Able to ID about 4000 PCs. I need to go back and look at the scource data right now. Thanks Again for your help.

UPDATE tgt
SET
CusomerID = src.CustomerID
FROM
database2..table2 as tgt
INNER JOIN
(SELECT DISTINCT CustomerID id, PCName
FROM database2..table1 c
INNER JOIN database1..table1 hw
ON c.CustomerName = hw.CustomerName) AS src
ON tgt.PCName = src.PCName

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources