Manage Learn to apply best practices and optimize your operations.

Managing common IDs in two separate databases

This is probably a simple question, but I seem to be butting my head against a brick wall.

Here's the scenario:

We have two databases, call them A and B. A is created when a user registers on a forum site here at the university, and B is their "profile".

There is a common ID in both files.

Because of a bug in the way Invision works (the forum software), there is one field in A that is out of sync with B. A.member_type is a numeric based on their "membership type" (student, teacher, etc) - single digit.

B.member_group is the membership type in text, based on what they put in A - for example, if A.member_type = 3, then B.member_group would be "Student". But both tables are editable by the user, so they don't match up (that is being fixed over Christmas !).

What I need to do is retrieve all records in both tables, joined by their ID, and change B.member_group based on the contents of A.member_type.

But every time I try it, I get various and sundry errors, and one of our developers has told me that it cannot be done programmatically. As it is not I who will be running this script, but someone with zero SQL knowledge, is there an easy way of doing it?

Without wanting to doubt your friend, it sounds like it should be fairly straightforward to do. You can join tables via columns that are CONVERTed or CAST to the appropriate type.

Dig Deeper on SQL Server Database Modeling and Design

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.