auto increment (identity) stored procedure for insert

Question

_{Thursday, May 30, 2019 1:11 PM}

hello friends,

i have a stored procedure in SQL server database for my page the problem is i have a primary key column with IDENTITY(1,1) but the problem is i have another column which i want to make it auto increment too which also number. below is my stored procedure code.

Note 1: the column to be auto increment is "PolicyNumber"

Note2: i did the procedure for XML because i have in the code behind option for Bulk insertion gridview

USE [uriic2018_traveldev]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

   ALTER PROCEDURE [dbo].[PolicyBulkInsert]

 @XMLData xml

AS

 Create table #tempPolicy(

  FirstName varchar(50) null,

  MiddleName varchar(50) null,

  LastName varchar(50) null,

  PolicyNumber bigint NULL

 ); 

 

 Insert into PolicyDetails(FirstName,MiddleName, LastName, PolicyNumber)

 

  Select  

  PolicyDetail.query('FirstName').value('.', 'varchar(50)') as FirstName,

  PolicyDetail.query('MiddleName').value('.', 'varchar(50)') as MiddleName,

  PolicyDetail.query('LastName').value('.', 'varchar(50)') as LastName,

  PolicyDetail.query('PolicyNumber').value('.', 'bigint') as PolicyNumber

  

 FROM

  @XMLData.nodes('/PolicyDetails/PolicyDetail')AS xmlData(PolicyDetail)

RETURN

All replies (4)

_{Thursday, May 30, 2019 1:57 PM}

Why do you need a second auto increment column?  What can't you use the Identity column?

_{Friday, May 31, 2019 6:42 AM}

Hi ahmedsalahaddin,

Not sure why you want the second auto increment  column,  but you could try to use max() to get the max value of PolicyNumber then add one(assume your another column is AnotherColumn)

Insert into PolicyDetails(FirstName,MiddleName, LastName, PolicyNumber)

  Select  

  PolicyDetail.query('FirstName').value('.', 'varchar(50)') as FirstName,

  PolicyDetail.query('MiddleName').value('.', 'varchar(50)') as MiddleName,

  PolicyDetail.query('LastName').value('.', 'varchar(50)') as LastName,


   ( select  (isnull(max(PolicyNumber),0)+1) from PolicyDetails)  -- get max value plus 1 if it is the first row set it to 1
  

 FROM

  @XMLData.nodes('/PolicyDetails/PolicyDetail')AS xmlData(PolicyDetail)

To prevent concurrency, please give  the second column a unique constraint.

Best regards,

Ackerly Xu

_{Friday, May 31, 2019 1:45 PM}

first of all... because an idiot designed this database and now it has more than 129000 records and there are many records deleted in between and the exist primary key has no any foundation in the page codes. anyway...

your suggestion was perfect and it makes the increment BUT... my stored procedure is designed for dynamic Bulk insert in my aspx page the user sometimes enters 3 records and times 5 records. so if the MAX+1 PolicyNumber is 123456 the whole 5 records will get 123456 as PolicyNumber when it should be 123456, 123457,123458...etc sequence. do you know how to solve this please ??

_{Friday, May 31, 2019 2:50 PM}

first of all... because an idiot designed this database and now it has more than 129000 records and there are many records deleted in between and the exist primary key has no any foundation in the page codes. anyway...

your suggestion was perfect and it makes the increment BUT... my stored procedure is designed for dynamic Bulk insert in my aspx page the user sometimes enters 3 records and times 5 records. so if the MAX+1 PolicyNumber is 123456 the whole 5 records will get 123456 as PolicyNumber when it should be 123456, 123457,123458...etc sequence. do you know how to solve this please ??

There's already 129000 records?  The how is the Policy Number currently being assigned?  Is this a new feature? 

It sounds like records do get deleted from time to time so how do you plan to handle deleted records?  

To answer your question you can use a create trigger to increment the Policy Number.

/en-us/sql/t-sql/statements/create-trigger-transact-sql?view=sql-server-2017