CODEWITHSUNDEEP
sqlsql-serverdatabase
Bella
Bella

Reputation: 87

ID auto increment and update in SQL Server database

Inside my SQL Server database I got this as my ID for example SSK0000001.

I want it to auto-increment in my system and also the last number of ID e.g."1" (after another new record it will become SSK0000002) is update in my SQL Server database.

I know it can be done with set auto-increment inside database but now my ID is nchar.

Anyone could help will be appreciate.

Upvotes: 0

Views: 2635

Answers (3)

Daniel PP Cabral
Daniel PP Cabral

Reputation: 1624

You could use a calculated column based on an auto-increment column. Should work from sql server 2005 upwards. 

CREATE TABLE [dbo].[TEST1](
    [AUTO_ID] [int] IDENTITY(1,1) NOT NULL,
    [ID]  AS CAST(('SSK'+replace(str([AUTO_ID],(7),(0)),' ','0')) AS NCHAR(10)) PERSISTED,
    [NAME] [nvarchar](10) NULL

) ON [PRIMARY]

GO

INSERT INTO TEST1 (NAME) VALUES ('NAME1')
INSERT INTO TEST1 (NAME) VALUES ('NAME2')
INSERT INTO TEST1 (NAME) VALUES ('NAME3')

SELECT * FROM TEST1

Upvotes: 2

dani herrera
dani herrera

Reputation: 51715

You can cast right side to int and increment:

declare @next int
select @next = convert( int, right( max( id ), 7 ) )  + 1
from your table

then insert as:

insert into ... (Id, ...)
values (   'SDK' + right( '0000000' + rtrim(ltrim( str( @next ) ) ), 7 ), ... )

But this is the most dirty solution as you can take:

  • This cause a lot of database locks (because aggregation function)
  • Slowest than autoincrement
  • Lot of code to keep.

I suggest to you to move to an autoincrement data type

Upvotes: 1

Aurélien Grimpard
Aurélien Grimpard

Reputation: 964

The first solution that come to my mind is

  • explode your ID so you separate SSK from the number

  • increment the number

  • then stick back SSK with the number

Upvotes: 0

Related Questions