CODEWITHSUNDEEP
sqlsql-server
Joe Starnes
Joe Starnes

Reputation: 491

Best way to generate a UniqueID for a group of rows?

This is very simplified but I have a web service array of items that look something like this:

[12345, 34131, 13431]

and I am going to be looping through the array and inserting them one by one into a database and I want that table to look like this. These values would be tied to a unique identifier showing that they were 

1 12345
1 34131
1 13431

and then if another array came along it would then insert all of its numbers with unique ID 2.... basically this is to keep track of groups.

There will be multiple processes executing this potentially at the same time so what would be the best way to generate the unique identifier and also ensure that 2 processes couldn't have used the same one?

Upvotes: 0

Views: 845

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

You should fix your data model. It is missing an entity, say, batches.

create table batches (
    batch_id int identity(1, 1) primary key,
    created_at datetime default getdate()
);

You might have other information as well.

And your table should have a foreign key reference, batch_id to batches.

Then your code should do the following:

  • Insert a new row into batches. A new batch has begun.
  • Fetch the id that was just created.
  • Use this id for the rows that you want to insert.

Although you could do this with a sequence, a separate table makes more sense to me. You are tying a bunch of rows together into something. That something should be represented in the data model.

Upvotes: 2

MaxPuissant
MaxPuissant

Reputation: 31

You can declare this : DECLARE @UniqueID UNIQUEIDENTIFIER = NEWID();

and use this as your unique identifier when you insert your batch

Upvotes: 1

Moose
Moose

Reputation: 202

Since it isn't a primary key, an identity column is out. Honestly I'd probably just track it using a separate id sequence table. Create a proc that grabs the next available ID and then increments it. If you open a transaction at the beginning of the proc it should prevent the second thread from getting the number until the first thread is done with it's update.

Something like:

CREATE PROCEDURE getNextID
     @NextNumber INT          OUTPUT
    ,@id_type    VARCHAR(20)
AS
BEGIN
  SET NOCOUNT ON;

    DECLARE @NextValue TABLE (NextNumber int);

BEGIN TRANSACTION;

    UPDATE id_sequence
      SET last_used_number = ISNULL(@NextNumber, 0) + 1
     OUTPUT inserted.last_used_number INTO @NextValue(NextNumber)
    WHERE id_type = @id_type

    SELECT @NextNumber = NextNumber FROM @NextValue

COMMIT TRANSACTION;

END

Upvotes: 0

Related Questions