SQL Server Ensuring unique ID for inserted batch of data

yebdmbv4 于 2023-04-28 发布在其他

关注(0)|答案(1)|浏览(93)

I have a process that allows users to insert data from Excel file into a single SQL Server table using VBA. Multiple users can insert data concurrently as they use the file independently from their workstations.

The SQL script from Excel creates one insert into statement only, so everything is inserted as a batch (presumably) by each user.

For example:

INSERT INTO TABLE (col1, col2)
VALUES (a,b), (c,d), (e,f);

I wrote an After Insert trigger on the table as follows to be able to identify & generate a unique ID for each batch (plus an 'extraID' for downstream purposes). Given the locks used, is it possible for the batch ID to be not unique given concurrent usage from the Excel files?

Could you recommend changes to ensure so if not? Thanks!

CREATE TRIGGER [dbo].[example] 
ON [dbo].[tblExample]
INSTEAD OF INSERT
AS 
BEGIN 
    BEGIN TRANSACTION;

    DECLARE @batchID AS int
    DECLARE @extraID AS int

    SELECT @batchID = COALESCE(MAX(BatchID), 0) + 1  
    FROM tblExample WITH (TABLOCKX, HOLDLOCK)
    
    SELECT @extraID = COALESCE(MAX(nodeID), 0) + 1 
    FROM tblExample WITH (TABLOCKX, HOLDLOCK)

    INSERT INTO [tblExample] WITH (TABLOCK)
        SELECT @batchID, @extraID, col1, col2 
        FROM inserted 
    
    COMMIT TRANSACTION;
END

sql-server

来源：https://stackoverflow.com/questions/76110920/ensuring-unique-id-for-inserted-batch-of-data

1条答案

按热度按时间

f4t66c6m1#

Like I mentioned in the comments, I would suggest using a SEQUENCE ; there's no need to "reinvent the wheel" here.

--Create sample table
CREATE TABLE dbo.YourTable (ID int IDENTITY(1,1),
                            BatchID int NOT NULL,
                            Col1 char(1),
                            Col2 char(2));
GO
--Create sample sequence
CREATE SEQUENCE dbo.YourSequence
    START WITH 1
    INCREMENT BY 1;
GO
--Create sample trigger
CREATE TRIGGER dbo.YourTrigger ON dbo.YourTable
INSTEAD OF INSERT AS
BEGIN
    --Get next value for sequence
    DECLARE @BatchID int = NEXT VALUE FOR dbo.YourSequence;
    --Insert values into table
    INSERT INTO dbo.YourTable (BatchID,
                               Col1,
                               Col2)
    SELECT @BatchID,
           Col1,
           Col2
    FROM inserted;
END;
GO
--INSERT containing multiple rows
INSERT INTO dbo.YourTable (Col1,Col2)
VALUES('a','b'),
      ('c','d'),
      ('e','f');
--insert containing single row
INSERT INTO dbo.YourTable (Col1,Col2)
VALUES ('x','y');
GO
--Check data
SELECT *
FROM dbo.YourTable;
GO
--Clean up
DROP TABLE dbo.YourTable
DROP SEQUENCE dbo.YourSequence;

The above SELECT returns the following:

ID	BatchID	Col1	Col2
1	1	a	b
2	1	c	d
3	1	e	f
4	2	x	y

赞(0）回复(0）举报 2023-04-28

我来回答

SQL Server Ensuring unique ID for inserted batch of data

1条答案

相关问题

热门标签

最新问答