May 30, 2019 at 1:31 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
May 30, 2019 at 1:40 pm
You could always use a SEQUENCE object to generate an ever increasing number
You would then use NEXT VALUE FOR in order to get the next number.
May 30, 2019 at 2:01 pm
You can't have multiple columns with the IDENTITY property. Why would you need 2 anyway, as even if you did, they'd have the same value. If you want that value to appear in another column, why not use a Computed Column?
ALTER TABLE YourTable ADD PolicyNumber AS CONCAT('Polno',YourIdentityColumn) PERSISTED;Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
May 31, 2019 at 1:06 am
it only adds number zero in the column of PolicyNumber
i added below Sequence in Sequence section in the database:
USE [uriic2018_traveldev]
GO
USE [uriic2018_traveldev]
GO
/****** Object: Sequence [dbo].[PolicyNumber] Script Date: 31/05/2019 3:45:36 AM ******/
CREATE SEQUENCE [dbo].[PolNo]
AS [bigint]
START WITH 129900
INCREMENT BY 1
MINVALUE -9223372036854775808
MAXVALUE 9223372036854775807
CACHE
GO
also i made an amendment in the stored procedure to add the code for Sequence:
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
);
BEGIN
DECLARE @PolicyNumb AS INT
Set @PolicyNumb = NEXT VALUE FOR PolNo
END
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('PolicyNumb').value('.', 'bigint') as PolicyNumber
FROM
@XMLData.nodes('/PolicyDetails/PolicyDetail')AS xmlData(PolicyDetail)
RETURN
May 31, 2019 at 4:17 am
So, you now get a number from the Sequence Object
DECLARE @PolicyNumb AS INT
Set @PolicyNumb = NEXT VALUE FOR PolNo
but you do not use it
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('PolicyNumb').value('.', 'bigint') as PolicyNumber
-- I suspect that you sould be using the following line instead
-- @PolicyNumb as PolicyNumber
FROM @XMLData.nodes('/PolicyDetails/PolicyDetail')AS xmlData(PolicyDetail)
May 31, 2019 at 1:18 pm
that worked.. thanks dear
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply