SQL Concatenate fields causing duplicates

Question

Hi,

i have some tables in our dimensional model that are concatenating 2 integer fields together to create a unique key.

for example we have a ReserveId and a SequenceNum field that are combined on each row, however we ran into a duplicate entry as follows

Reserve ID = 7931, SequenceNum = 1
Reserve ID = 793, SequenceNum = 13

So therefore the new concatenated fields were the same (79313) after combining the fields and it created a duplicate entry.

Is there a better way to combine the fields so that they are unique? These fields are used in 2 different tables and are also used for joining the tables together so they would have to be the same in each table.

thanks
Scott

Answer 1

Hi @Cabral, Scott ，

Welcome to Microsoft Q&A!

--DDL  
create table tablec  
(ReserveID int,  
SequenceNum int)  
  
insert into tablec values  
(7931,1),  
(793,31)  

You could try to multiply one with a high enough value.

SELECT ReserveID * 1000 + SequenceNum from tablec  
--7931001  
--793031  

Or use text concatenation:

SELECT CAST(CAST(ReserveID AS nvarchar(10)) + RIGHT('0' + CAST(SequenceNum AS nvarchar(10)), 6) AS int)   
from tablec  
--793101  
--793031  

Or skip the integer thing and separate the IDs with something non-numeric:

SELECT CAST(ReserveID AS nvarchar) + ':' + CAST(SequenceNum AS nvarchar)  
from tablec  
--7931:1  
--793:31  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

You can also make a bigint from two positive ints:

declare @ReserveID int = 793
declare @SequenceNum int = 13

declare @concatenated bigint = (cast(@ReserveID as bigint) * 0x100000000) | @SequenceNum

But you will have to change some of your variables and columns to bigint.

Answer 3

IMHO, the best way to do this is to use a composite index. That would look something like

Create Table Reserve(ReserveID int Not Null Primary Key, OtherReserveInfo varchar(30));  
Create Table SequenceData(SequenceID int Not Null Primary Key, OtherSequenceData varchar(30) Not Null);  
Create Table ReserveSequenceDataXref(ReserveID int Not Null References Reserve,   
  SequenceID int Not Null References SequenceData, OwnerType varchar(30) Not Null  
  Primary Key (ReserveID, SequenceID));  
Insert Reserve(ReserveID, OtherReserveInfo) Values  
(7931, 'abc'),  
(793, 'def');  
Insert SequenceData(SequenceID, OtherSequenceData) Values  
(3, 'xyz'),  
(13, 'uvw');  
Insert ReserveSequenceDataXref Values  
(7931, 3, 'Joint'),  
(793, 13, 'TenCom');  
Select * From ReserveSequenceDataXref;  

Result
ReserveID SequenceID OwnerType
----------- ----------- ------------------------------
793 13 TenCom
7931 3 Joint

That avoids all the complex handling of combining the two id's and splitting them out, guarantees no duplicates where both ReserveID and SequenceID are the same, but will not confuse 793, 13 with 7931,3.

Tom