sql server - Auto Increment SQL Value -

in infinte wisdom of global dba @ firm working @ right has created table takes int id field, not auto increment number.

i passing table valued parameter .net because has 100 or more rows of data being passed @ 1 time , dont want kill application, hammer network or sql server.

so stored procedure

create procedure sp_insert_supporting_error_info  (@tvp [dbo].udt_ceqzw readonly)  begin  insert ceqzw  error_var_id,  error_id,  error_var_type_cd,  error_var_value)  select (select coalesce(max(error_var_id), 0) + row_number() on (order  (select null)) ceqzw) error_var_id,  error_id,  error_var_type_cd,  error_var_value @tvp end go 

i hoping select coalesce(max(error_var_id), 0) + row_number() on (order (select null)) ceqzw how trick me when test

declare @p3 dbo.udt_ceqzw insert @p3 values(1,n'es',n'test') insert @p3 values(1,n'ec',n'test') insert @p3 values(1,n'ec',n'test') insert @p3 values(1,n'ses',n'test') insert @p3 values(1,n'es',n'test')  exec sp_insert_supporting_error_info @p3 

this

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected) msg 2627, level 14, state 1, procedure sp_insert_supporting_error_info, line 9 violation of primary key constraint 'pk_ceqzw'. cannot insert duplicate key in object 'dbo.ceqzw'. duplicate key value (1). statement has been terminated.

so question have how i, other hammering network, app , sql server auto increment , add id table

well, start going dba , ask why decided not make id column , identity. perhaps change mind.

if, however, keep decision, not attempt create auto-increment mechanism on own.
99.9% of cases has potential fail, in multi user environment.
instead, use built in, thread safe method of identity column.

since talking situation can't use identity column directly in target table, suggest using simple mimic of sequence object introduced in 2012 version auto increment.

for this, you'll need tally (numbers) table. if dba did not create one, send him to read jeff moden's the "numbers" or "tally" table: , how replaces loop , send him km.'s answer on this post creation script. (method 7 favorite.)

now have numbers table, add simple table:

create table tblsequence (     value int identity(1,1) ) 

then, create stored procedure insert number of rows table , returns newly created values (thanks martin smith merge trick on this post!):

create procedure stp_getnextvalues (     @numberofvalues int )      merge sequence     using (select number              tally             number <= @numberofvalues) t     on 1 = 0     when not matched       insert       default values     output inserted.value;   go 

then whenever execute stored procedure you'll safe auto incremented values.

exec stp_getnextvalues 125 

you can see full script in action on rextester.

i leave incorporate own procedure.