By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,426 Members | 2,928 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,426 IT Pros & Developers. It's quick & easy.

Creating a key field from selective data in other fields -- is it possible?

P: 3
I want to use Group_ID as the primary key in all of my tables. Unfortunately, the uploaded data has group IDs from two systems: OLD_ID and NEW_ID. Ultimately, all groups will have a NEW_ID number, but for now, some do and some don't. This means that some groups only have OLD_ID, some have both NEW_ID and OLD_ID, and some only have NEW_ID.

What I want to do is pull the information from NEW_ID and OLD_ID into a new field in a separate table, GROUP_ID (the new field is also called Group_ID). The logic I want goes like this:

IF NEW_ID is not blank, use NEW_ID; ELSE use OLD_ID.

In the original table, there would be no primary key, since both OLD_ID and NEW_ID have null values in some records. Both of these fields would link to primary key Group_ID in the GROUP_ID table, and all other tables would link to Group_ID via that table as well.

My restrictions are that I can't change anything in the uploaded data, and that I only want to pull up one of the ID numbers (in the event that a group has both).

My questions:
(1) Is this possible to do?
(2) If it is possible, what is the SQL code and where would I put it?
(3) If it is not possible, are there other solutions?
Apr 15 '10 #1
Share this Question
Share on Google+
5 Replies

Expert Mod 15k+
P: 31,494
If the data you intend to upload doesn't already contain a [GROUP_ID] value, then this could not be uploaded directly into place. However, with the logic you posted, and always assuming that all records must have one or the other, the data could be uploaded into a holding table, then transferred from there using the specified logic.

The SQL for transferring the data would be similar to :
Expand|Select|Wrap|Line Numbers
  1. INSERT INTO [MainTable] ( {Field List} )
  3. SELECT      Nz([NEW_ID],[OLD_ID]) AS [Group_ID],
  4.             *
  6. FROM        [HoldingTable]
Don't forget to delete any data from the holding table that has been successfully transferred.

Welcome to Bytes!
Apr 15 '10 #2

P: 3
I must ask: is this the only option available to me? I ask because I would prefer not to combine all the OLD_ID and NEW_ID values into one field, as some groups have both and this would cause them to have duplicate records. Ideally, I want only the NEW_ID, unless it doesn't exist, and only then would I take the OLD_ID.
Apr 15 '10 #3

Expert Mod 15k+
P: 31,494
What I posted gives you :
  1. [Group_ID] set to the value of [NEW_ID] if it has a value otherwise [OLD_ID] if not.
  2. All other original fields, presumably including both [OLD_ID] and [NEW_ID], regardless of what data they may, or may not, contain.
It seems from your question that you didn't appreciate this. If you did, then I don't quite understand your question, but will happily check back if you wish to clarify.
Apr 15 '10 #4

P: 3
Sorry, I don't really understand SQL. Thank you for your clarification; I will try the code and see if I can get it right.
Apr 15 '10 #5

Expert Mod 15k+
P: 31,494
Let us know if you struggle.

Post the SQL you try if it fails.
Apr 15 '10 #6

Post your reply

Sign in to post your reply or Sign up for a free account.