How to assign unique identifier to rows that are common

Anonymous 61 Reputation points
2020-12-12T18:16:51.987+00:00

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.

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,547 questions
{count} votes

Accepted answer
  1. EchoLiu-MSFT 14,571 Reputation points
    2020-12-14T07:47:43.03+00:00

    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


3 additional answers

Sort by: Most helpful
  1. Viorel 111.8K Reputation points
    2020-12-12T19:18:58.597+00:00

    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.

    1 person found this answer helpful.
    0 comments No comments

  2. Erland Sommarskog 100.8K Reputation points MVP
    2020-12-12T20:04:57.337+00:00

    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
    
    1 person found this answer helpful.
    0 comments No comments

  3. Joe Celko 16 Reputation points
    2020-12-14T20:34:29.09+00:00

    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!

    0 comments No comments