Let's say I have table A with an alphanumeric string primary key. The code used to create the table and how the table looks are shown below.
CREATE TABLE A
(
ID CHAR(7) NOT NULL,
...
CONSTRAINT PK_A PRIMARY KEY (ID)
)
| ID | ... |
| -------- | -------- |
| C000001 | ... |
| C000002 | ... |
I want to insert a new row into Table A and I don't want to type out C000003
or C000004
every time. Is there a way to auto increment this?
I have thought about getting the latest id using
select top 1 CustId
from Customer
order by CustId desc
For splitting, I used SUBSTRING(ID, 2, 7)
. For joining back, I can use concat('C', ID 1)
.
The issue is, if I add one to the numeric portion, it would give me 3 instead of 000003. Is there a way to save the 0's?
I just need help with incrementing.
My current code is like this:
declare @lastid varchar(7), @newID varchar(7)
set @lastid = (select top 1 ID from A order by ID desc)
set @newID = SUBSTRING(@lastid, 2, 7)
select CONCAT('C', @newID 1) -- need help with the increment
Any help appreciated
EDIT 1: If the numbers are less than 10 (ie one digit), I can manually add in 0's to fill up the gaps. But if the number has 2 digits or more, I can't do that so I'm thinking of a solution for this.
CodePudding user response:
One word of advice: DON'T DO THIS! Using this SELECT MAX() 1
approach is not safe under load, as soon as more than one user will be using your application, you WILL HAVE DUPLICATES - sooner or later.
The only viable solution is to use
- an
ID INT IDENTITY(1,1)
column to get SQL Server to handle the automatic increment of your numeric value - a computed, persisted column to convert that numeric value to the value you need
So try this:
CREATE TABLE dbo.Customer
(
ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
CustomerID AS 'C' RIGHT('000000' CAST(ID AS VARCHAR(6)), 6) PERSISTED,
-- .... your other columns here....
)
Now, every time you insert a row into Customer
without specifying values for ID
or CustomerID
:
INSERT INTO dbo.Customer(Col1, Col2, ..., ColN)
VALUES (Val1, Val2, ....., ValN)
then SQL Server will automatically and safely increase your ID
value, and CustomerID
will contain values like C000001
, C000002
,...... and so on - automatically, safely, reliably, no duplicates.
CodePudding user response:
I think it's not any suggested way that automatically incrementaly change the char value. By logic we can do. Below is method which is perfect method for achive this effectivly.
Suppose you have one table called stminternal having following column Code, Prefix, Digit, LastNo
Insert into stminternal values(1,'C',6,0)
So using this table you can generate new string. First time WHEN RECORD INSERT C000001.
UPDATE stminternal SET LASTNO=2 WHERE CODE=1
Such a way it can be work like charms