SQL Identify Multiple Insert Rows from Single Transaction

2018-02-02 21:24:48

Is there method to identify multiple rows from single insert transaction?

IdentityId will uniquely identify each row. If 2000 rows were inserted from one insert transaction, how would I identify this? Is there like a BatchId() Identifier or TransactionId() reserved word? Using ETL default getdate() will not work, sometimes a transaction could span over milliseconds.

EG:

begin transaction

insert into dbo.Test2

select * from Test where ColumnA = 'etc...;

commit transaction

Refraining from using pattern below, may be inefficient, is there a reserved keyword for batch transaction identifier?

select @MaxTransactionId = (select Max(transactionId) from dbo.Test2),

TransactionId = @MaxTransactionId + 1,

Without knowing more about your use case, I'll suggest just storing it in the table itself in a simple, albeit klunky, way. If its for a one-off type activity it should work fine:

BEGIN TRANSACTION;

INSERT INTO dbo.Test2

SELECT *, RAND() AS [batch_id]

  • Without knowing more about your use case, I'll suggest just storing it in the table itself in a simple, albeit klunky, way. If its for a one-off type activity it should work fine:

    BEGIN TRANSACTION;

    INSERT INTO dbo.Test2

    SELECT *, RAND() AS [batch_id]

    FROM Test;

    COMMIT TRANSACTION;

    Now each transaction will have a unique RAND() value to group the rows together.

    2018-02-02 21:43:52