Database / User Table Issue

[#1]

Here is my Scenario:

I have 5 databases. There is a User Table in each of the databases.  There are a UserID and OwnerID in each of the User Tables.   So here is where it gets sticky....The same UserID and OwnerID exists in each of the databases.  In my opinion not designed well but it is a third party application we are using.  So David could be UserID 123 and OwnerID 345 in one database.  John could be UserID 123 and OwnerID 345 in a different database.  When I go to combine all the users together I don't have a unique key for each User.  Is there a way around this.  I was thinking if I could read the name of the database then I could concatenate that with UserID or something.  Appears that the way the third party designed it it was only suppose to be one database.  Any thought?

[#2 · Accepted solution]

Hi David, We had these problems some times while using third party systems data.

Here are the options:

1. are you having  only UserID & OwnerID ? or If you have more fields in the tables, then try to make composite key with help of other fields to create the Primary Key. (I believe, you have Creation or Insert Date)

2. As you mentioned, use the DB Name in to create the Primary Key like Below:

ODBC CONNECT DB1;

USER:

LOAD * , 'DB1'&UserID&OwnerID AS %PrimaryKey ;

SQL SEELCT * FROM USER;

ODBC CONNECT DB2;

LOAD * , 'DB1'&UserID&OwnerID AS %PrimaryKey ;

SQL SEELCT * FROM USER;

ODBC CONNECT DB3;

LOAD * , 'DB1'&UserID&OwnerID AS %PrimaryKey ;

SQL SEELCT * FROM USER;

ODBC CONNECT DB4;

LOAD * , 'DB1'&UserID&OwnerID AS %PrimaryKey ;

SQL SEELCT * FROM USER;

ODBC CONNECT DB5;

LOAD * , 'DB1'&UserID&OwnerID AS %PrimaryKey ;

SQL SEELCT * FROM USER;