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

[RachelOrlando]

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?

[NeoPa]

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 :

INSERT INTO [MainTable] ( {Field List} )
 
SELECT      Nz([NEW_ID],[OLD_ID]) AS [Group_ID],
            *
 
FROM        [HoldingTable]

Don't forget to delete any data from the holding table that has been successfully transferred.

Welcome to Bytes!

[RachelOrlando]

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.

[NeoPa]

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.

[RachelOrlando]

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.

[NeoPa]

Let us know if you struggle.

Post the SQL you try if it fails.