0

I have a table where one of the columns is a path to an image and I need to create a directory for the record being inserted.

Example:

Id | PicPath |<br>
 1 | /Pics/1/0.jpg|<br>
 2 | /Pics/2/0.jpg|

This way I can be sure that the folder name is always valid and it is unique (no clash between two records).

Question is: how can I safely refer to the current id of the record being insert? Keep in mind that this is a highly concurrent environment, and I would like to avoid multiple trips to the DB if possible.

I have tried the following:

insert into Dummy values(CONCAT('a', (select IDENT_CURRENT('Dummy'))))

and

insert into Dummy values(CONCAT('a', (select SCOPE_IDENTITY() + 1)))

The first query is not safe, for when running 1000 concurrent inserts I got 58 'duplicate key' exceptions.

The second query didn't work because SCOPE_IDENTITY() returned the same value for all queries as I suspected.

What are my alternatives here?

James Z
  • 12,209
  • 10
  • 24
  • 44
victor
  • 1,532
  • 1
  • 13
  • 32
  • Check out this answer: http://stackoverflow.com/questions/42648/best-way-to-get-identity-of-inserted-row – pmbAustin Mar 31 '17 at 21:47
  • I have, but using output would require partial insert -> get id from output -> update record using it, and I would like to perform a single operation – victor Mar 31 '17 at 21:52
  • @victor the OUTPUT clause is the only guaranteed method for concurrent scenarios. Everything else has concurrency issues. – Imran Saeed Mar 31 '17 at 22:02
  • victor ...what @i.net said ... if your in a highly concurrent scenario, this is pretty much the only way to do it. – pmbAustin Apr 03 '17 at 16:31

1 Answers1

0

Try a temporary table to track your inserted ids using OUTPUT clause

INSERT #temp_ids(someval) OUTPUT inserted.identity_column

This will get all the inserted ids from your queries. 'inserted' is context safe.

Imran Saeed
  • 3,414
  • 1
  • 16
  • 27