I have created a single table in my primary DB called tblGlobalIDMapping
that will assign a GUID to all entries and store the other 3 primary ID's across the App to provide for a single ID repository.
The new Table is in the following structure -->
AppGlobalID uniqueidentifier NoNulls
PersonID int NoNulls
DRIMaster int Nulls
DRIClient int Nulls
The information I need is in a table in a different DB (same server) with the following partial structure -->
PersonID int NoNulls
ApplicationID tinyint NoNulls
PersonApplicationID varchar(14) NoNulls
A person(ID# 13579) is represented in the second table 1 for each Application. The ApplicationID column stores a number, 1 for DRIMaster and 6 for DRIClient, that tells you what the ID# in PersonApplicationID is for.
I need to do a Bulk Copy/Insert from that table to my new table but I want only 1 row recording the 3 main ID's in the new table.
This is what I have so far but this returns a row for each PersonID.
SELECT PersonID,
CASE
WHEN ApplicationID = 1
THEN PersonApplicationID
END AS 'DRIMaster',
CASE
WHEN ApplicationID = 6
THEN PersonApplicationID
END AS 'DRIClient'
FROM tblApplicationAssociation
WHERE ApplicationID IN (1,6)
This returns the below -->
PersonID DRIMaster DRIClient
_______________________________
108574 71163 NULL
108574 NULL 71163