SQLTeam.com | Weblogs | Forums

Creating a unique Batch ID for Update

Hi There,

I'm trying to write a query that sets a batch of rows to have a single uniquely generated ID on the fly. Tried lots of things and just can't get it right.

Below are sample scripts to create the table and insert some test data along with two update queries that do not give me the desired results. I understand why they do not achieve what I'm looking for they are really there to show the kind of things I have tried.

Here is an image of what i want the reults to look like. All ref numbers that match have the same uniquely generated ID. Any input would be appreciated, i might have to go a different route and happy to do so if there is a better option. Thanks for looking.

SQLCapture

-- CODE SAMPLES

CREATE TABLE ##temp (
ref VARCHAR(MAX),
batch_ID VARCHAR(MAX)
);

INSERT INTO ##temp (ref) values(1)
INSERT INTO ##temp (ref) values(1)
INSERT INTO ##temp (ref) values(1)
INSERT INTO ##temp (ref) values(2)
INSERT INTO ##temp (ref) values(2)
INSERT INTO ##temp (ref) values(2)
INSERT INTO ##temp (ref) values(3)
INSERT INTO ##temp (ref) values(4)
INSERT INTO ##temp (ref) values(5)
INSERT INTO ##temp (ref) values(5)

select * from ##temp
-- DELETE from ##temp

-- update queries
--1
DECLARE @random_guid VARCHAR(250);
SET @random_guid = NEWID()
update ##temp set batch_id = @random_guid where ref=REF
--2
update ##temp set batch_id = NEWID() where ref=REF

DROP TABLE ##temp

I changed from Global Temp table to a temp table. Also, I changed the tables to have the correct data types. Then, I copied all distinct ref values to another temp table with a UniqueIdentifier column with a default of newid. Finally, I updated the first temp table from the second. I don't know what kind of volume you have, but indexes will help if needed.

CREATE TABLE #temp (
ref int,
batch_ID UniqueIdentifier
);
 Create table #RandomGuid (Ref int, Batch_ID UniqueIdentifier default newid());

INSERT INTO #temp (ref) values(1)
INSERT INTO #temp (ref) values(1)
INSERT INTO #temp (ref) values(1)
INSERT INTO #temp (ref) values(2)
INSERT INTO #temp (ref) values(2)
INSERT INTO #temp (ref) values(2)
INSERT INTO #temp (ref) values(3)
INSERT INTO #temp (ref) values(4)
INSERT INTO #temp (ref) values(5)
INSERT INTO #temp (ref) values(5)

select * from #temp

insert into #RandomGuid (Ref) select distinct Ref from #Temp

update t
   set batch_ID = g.batch_ID
 from #temp t
	join #RandomGuid g
		on t.ref = g.ref

select * from #temp
2 Likes
CREATE TABLE ##temp (
ref VARCHAR(MAX),
batch_ID VARCHAR(MAX)
);

INSERT INTO ##temp (ref,batch_ID) values
   (1,NEWID()),(1,NEWID()),(1,NEWID()),
   (2,NEWID()),(2,NEWID()),
   (3,NEWID()),
   (4,NEWID()),
   (5,NEWID()),(5,NEWID())

UPDATE t
SET batch_ID = t2.batch_ID
FROM ##temp t
INNER JOIN (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY ref ORDER BY NEWID()) AS row_num
    FROM ##temp
) AS t2 ON t2.ref = t.ref AND t2.row_num = 1

SELECT * FROM ##temp
1 Like

Thanks Mike that's great!

Thanks Scott, appreciate the help.