How to assign unique identifier to rows that are common

Question

I have a table as below:

Create Table Gifts (
FName varchar (50) NOT NULL, 
LName varchar (50) NOT NULL,
Project varchar (50) NOT NULL, 
Amount decimal NOT NULL, 
TransactionDate Datetime NOT NULL, 
TransId int NOT NULL, 
GiftImportId varchar (20) NUll
)

Insert into Gifts values ('Adam', 'Smith', 'Water for life', 15, GETDATE(), 4000, NULL)
Insert into Gifts values ('Chris', 'Gayle', 'Orphans and Children', 10, GETDATE(), 4001, NULL)
Insert into Gifts values ('Chris', 'Gayle', 'Emergency Response', 10, GETDATE(), 4001, NULL)
Insert into Gifts values ('Chris', 'Gayle', 'Where Most Needed', 30, GETDATE(), 4001, NULL)
Insert into Gifts values ('David', 'Warner', 'Winter Appeal', 20, GETDATE(), 4002, NULL)
Insert into Gifts values ('Ahmed', 'Sabil', 'Global Coronavirus Appeal', 10, GETDATE(), 4003, NULL)
Insert into Gifts values ('Ahmed', 'Sabil', 'Emergency Response', 30, GETDATE(), 4003, NULL)

I need to populate the GiftImportId column with a 20 digit unique value. The gifts will be imported onto a CRM and it requires import id to be unique. I am going to use CRYPT_GEN_RANDOM(9) to generate the 20 digit unique value for the GiftImportId. But where the TransId is the same it needs to have the same GiftImportId. So for example, where TransId is 4001 it needs to have the same import id for all rows where the TransId is 4001. Please advise how I could achieve this using T-SQL query. Thanks.

Answer 1

Hi @Ruhul-0167，

The following method can also not create an intermediate table in advance：

     Create Table Gifts (  
     FName varchar (50) NOT NULL,   
     LName varchar (50) NOT NULL,  
     Project varchar (50) NOT NULL,   
     Amount decimal NOT NULL,   
     TransactionDate Datetime NOT NULL,   
     TransId int NOT NULL,   
     GiftImportId varchar (20) NUll  
     )  
          
     Insert into Gifts values ('Adam', 'Smith', 'Water for life', 15, GETDATE(), 4000, NULL)  
     Insert into Gifts values ('Chris', 'Gayle', 'Orphans and Children', 10, GETDATE(), 4001, NULL)  
     Insert into Gifts values ('Chris', 'Gayle', 'Emergency Response', 10, GETDATE(), 4001, NULL)  
     Insert into Gifts values ('Chris', 'Gayle', 'Where Most Needed', 30, GETDATE(), 4001, NULL)  
     Insert into Gifts values ('David', 'Warner', 'Winter Appeal', 20, GETDATE(), 4002, NULL)  
     Insert into Gifts values ('Ahmed', 'Sabil', 'Global Coronavirus Appeal', 10, GETDATE(), 4003, NULL)  
     Insert into Gifts values ('Ahmed', 'Sabil', 'Emergency Response', 30, GETDATE(), 4003, NULL)  
      
     SELECT DISTINCT TransID,GiftImportId   
     INTO #Test  
     FROM Gifts  
       
     UPDATE #Test  
     SET GiftImportId = CONVERT(char(20), CRYPT_GEN_RANDOM(10),2)  
       
     UPDATE G  
     SET    GiftImportId = T.GiftImportId  
     FROM Gifts G  
     JOIN #Test T  
     ON G.TransID=T.TransID  
        
     SELECT * FROM Gifts  
      
     DROP TABLE #Test  
     DROP TABLE Gifts  

Output:

    FName LName Project Amount TransactionDate TransId GiftImportId  
     Adam Smith Water for life 15 2020-12-14 15:41:34.430 4000 FC633C2446CDF3EEC775  
    Chris Gayle Orphans and Children 10 2020-12-14 15:41:34.430 4001 52E03A2A575D7A0F7CA3  
    Chris Gayle Emergency Response 10 2020-12-14 15:41:34.430 4001 52E03A2A575D7A0F7CA3  
    Chris Gayle Where Most Needed 30 2020-12-14 15:41:34.430 4001 52E03A2A575D7A0F7CA3  
    David Warner Winter Appeal 20 2020-12-14 15:41:34.430 4002 ED4582CF0489933585CD  
    Ahmed Sabil Global Coronavirus Appeal 10 2020-12-14 15:41:34.430 4003 DF4ABDA2D3BA5D4BA2B9  
    Ahmed Sabil Emergency Response 30 2020-12-14 15:41:34.430 4003 DF4ABDA2D3BA5D4BA2B9  

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Regards
Echo

---

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.
Hot issues November--What can I do if my transaction log is full?
Hot issues November--How to convert Profiler trace into a SQL Server table

Answer 2

If you cannot use TransId as new identifiers, then check the next example:

drop table if EXISTS #Gifts  
drop table if EXISTS #NewIds  
  
Create Table #Gifts (  
 FName varchar (50) NOT NULL,   
 LName varchar (50) NOT NULL,  
 Project varchar (50) NOT NULL,   
 Amount decimal NOT NULL,   
 TransactionDate Datetime NOT NULL,   
 TransId int NOT NULL,   
 GiftImportId varchar (20) NUll  
)  
      
Insert into #Gifts values ('Adam', 'Smith', 'Water for life', 15, GETDATE(), 4000, NULL)  
Insert into #Gifts values ('Chris', 'Gayle', 'Orphans and Children', 10, GETDATE(), 4001, NULL)  
Insert into #Gifts values ('Chris', 'Gayle', 'Emergency Response', 10, GETDATE(), 4001, NULL)  
Insert into #Gifts values ('Chris', 'Gayle', 'Where Most Needed', 30, GETDATE(), 4001, NULL)  
Insert into #Gifts values ('David', 'Warner', 'Winter Appeal', 20, GETDATE(), 4002, NULL)  
Insert into #Gifts values ('Ahmed', 'Sabil', 'Global Coronavirus Appeal', 10, GETDATE(), 4003, NULL)  
Insert into #Gifts values ('Ahmed', 'Sabil', 'Emergency Response', 30, GETDATE(), 4003, NULL)  
  
select * from #Gifts  
  
---  
  
create table #NewIds ( TransId int, GiftImportId varchar (20) )  
  
while 0 = 0  
begin  
    insert #NewIds  
    select TransId, convert(char(20), CRYPT_GEN_RANDOM(10), 2) as GiftImportId  
    from #Gifts  
    where GiftImportId is null   
    group by TransId  
  
    if @@ROWCOUNT = 0 break  
  
    update #Gifts  
    set GiftImportId = i.GiftImportId  
    from #Gifts g  
    inner join #NewIds i on i.TransId = g.TransId   
    where i.GiftImportId not in (select GiftImportId from #Gifts where GiftImportId is not null)  
  
    truncate table #NewIds  
end  
  
select * from #Gifts  

It assumes that you need hexadecimal identifiers.

It uses a loop to avoid possible theoretical duplicates, generated by random number generator. If you believe that the probability is negligible, you can remove the looping.

Answer 3

Here is a solution without a loop:

Create Table Gifts (
 FName varchar (50) NOT NULL, 
 LName varchar (50) NOT NULL,
 Project varchar (50) NOT NULL, 
 Amount decimal NOT NULL, 
 TransactionDate Datetime NOT NULL, 
 TransId int NOT NULL, 
 GiftImportId varchar (20) NUll
 )

 Insert into Gifts values ('Adam', 'Smith', 'Water for life', 15, GETDATE(), 4000, NULL)
 Insert into Gifts values ('Chris', 'Gayle', 'Orphans and Children', 10, GETDATE(), 4001, NULL)
 Insert into Gifts values ('Chris', 'Gayle', 'Emergency Response', 10, GETDATE(), 4001, NULL)
 Insert into Gifts values ('Chris', 'Gayle', 'Where Most Needed', 30, GETDATE(), 4001, NULL)
 Insert into Gifts values ('David', 'Warner', 'Winter Appeal', 20, GETDATE(), 4002, NULL)
 Insert into Gifts values ('Ahmed', 'Sabil', 'Global Coronavirus Appeal', 10, GETDATE(), 4003, NULL)
 Insert into Gifts values ('Ahmed', 'Sabil', 'Emergency Response', 30, GETDATE(), 4003, NULL)

 CREATE TABLE #keymap (TransID  int PRIMARY KEY, GiftImportId char(20))

 INSERT #keymap(TransID)
    SELECT DISTINCT TransID FROM Gifts

UPDATE #keymap
SET    GiftImportId = convert(char(20), CRYPT_GEN_RANDOM(10), 2)

UPDATE Gifts
SET    GiftImportId = k.GiftImportId
FROM   Gifts G
JOIN   #keymap k ON G.TransId = k.TransID
WHERE  G.GiftImportId IS NULL

SELECT * FROM Gifts ORDER BY TransId

Answer 4

FBy definition, a table must have a key. You have none. To be a First Normal Form table, it must model one set of entities or only one relationship. I'm also. You got 50 as the length of a last name, first name or project name. You also don't know that Microsoft is been able to do row constructor insertions for many years now. There is no need to write your code is if you were still in 1960, Sybase. Likewise, GETDATE() was replaced with the ANSI/ISO standard current_timestamp years ago. The Postal Union thinks that a name on an envelope should be a total of 35 characters long; what research did you do that said, you need 100? Also, because identifiers are on a nominal scale, they can never be numeric.

CREATE TABLE Donors
(donor_id CHAR(10) NOT NULL PRIMARY KEY,
donor_first_name VARCHAR(20) NOT NULL,
donor_last_name VARCHAR(20) NOT NULL,
.. );

CREATE TABLE Projects
(project_name VARCHAR(50) NOT NULL PRIMARY KEY
..);

CREATE TABLE Gifts
(transaction_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL,
gift_amt DECIMAL (12, 2) NOT NULL
CHECK (gift_amt >= 0.00),
donor_id CHAR(10) NOT NULL REFERENCES Donors,
project_name VARCHAR(50) NOT NULL REFERENCES Projects,
PRIMARY KEY (transaction_date, donor_id, project_name));

Please note how a gift is a relationship between a donor and a project. That's why we have references clauses back to those tables in our gifts. Notice the use of a default in the DDL instead of the insertion statements. This will let the optimizer do some nice tricks with it. You do need to create a donor ID, probably some kind of tax code so they can get credit for their gift.

> I need to populate the GiftImportId column with a 20 digit unique value. <<

Why? There's already a natural key that you should be able to discover from your raw data. Also, I can only think of about three ISO standards that are 20 digits long. Because identifiers none of them would be integers. Let me make a guess, based on 30 years of doing this, that you're still thinking about having one column keys , just like you would have in a magnetic tape file system.. But there's nothing wrong with the multicolumn key in RDBMS. We will actually wind up a shorter identifier than your 20 digits!