September 11, 2008 at 8:28 am
Hello all,
I'm desperately seeking some help from the masters with something that's very complicated to me being a newb. I have two database on two different servers. On database A there is a table named users_ that contains a column named facilitycode. On database B there is a table called ORGUsers that contains a column named OFFICE_LOCATION. Recently quite a few people have been shuffled around at the company and I need to update their facility code(location) in this one small system that is completely different and independent of the main system.
The users_.facilitycode column in database A contains values with a format like 010ORG01 or 010ORG06. These codes correlate to the values of the ORGUsers.OFFICE_LOCATION column in database B but with a different format. The values in the ORGUsers.OFFICE_LOCATION column are for example-Headquarters or Northern Department. So in effect Headquarters = 010ORG01 and Northern Department = 010ORG06. Currently the data in the OrgUsers.OFFICE_LOCATION column is current and the data in the users_.facilitycode column is outdated. I have also created a table named FacilityToLocation with two columns, FACILITYCODE and LOCATION in database A that acts as a key to match the facility codes with the office location values and i'm hoping it can be used to achieve my goal.
I managed to create a query:D that displays the different columns from both databases via a join statement so I hope i'm close to success at this point. What I need to know now is how to look at the ORGUsers.OFFICE_LOCATION column value in database B which holds current and correct data, then look at the FacilityToLocation table in database A to determine what the matching facility code is and then update the users_.facilitycode column in database A with the current facility code for each row (~2000 of them). Again, the data currently in the users_.facilitycode column is outdated.
Would anybody happen to have a similar script laying around? Any assistance would be most appreciated as I really do not wish to update all of these rows manually although I will if I have to.
Sorry about the long post and thanks for helping a newb in advance
September 11, 2008 at 9:04 am
You would use an UPDATE statement to change the data.
update tableB
set tableB.col = a.col
from tableA a
where TableB.othercol = a.othercol
The query syntax you have for SELECT would be used in the FROM part and WHERE part to customize the join.
If you show what you've done, perhaps we can help.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
September 11, 2008 at 9:19 am
Thanks for the response.
All I have so far is just a query that shows the two discussed columns plus a couple of others:
SELECT a.identificationnumber, a.facilitycode, b.OFFICE_LOCATION, a.deptname, a.fullname
FROM DATABASE_A.dbo.Users_ a
INNER JOIN SERVER_2.DATABASE_B.dbo.ORGUsers b
ON CAST(b.code_no AS Varchar(5)) = a.IdentificationNumber
WHERE facilitycode like '%ORG%'
ORDER BY CASE WHEN ISNUMERIC(a.identificationnumber) = 1 THEN CONVERT(int, a.identificationnumber) ELSE 0 END
GO
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply