CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Aldrin
Aldrin

Reputation: 766

MSSQL auto-increment ID with Prefix

I want to achieve an auto increment ID with prefix but resetting the number if it has different prefix.

The output I want looks like this:

ID  PREFIX  PROJECTID
1   PID_    PID_1
2   PID_    PID_2
3   RID_    RID_1
4   RID_    RID_2

But the result I got with my script is this:

ID  PREFIX  PROJECTID
1   PID_    PID_1
2   PID_    PID_2
3   RID_    RID_3
4   RID_    RID_4

Here's my script to create the table

CREATE TABLE PROJECTS
(ID INT IDENTITY(1,1) NOT NULL,
PREFIX NVARCHAR(10) NOT NULL,
PROJECTID AS ISNULL(PREFIX + CAST(ID AS NVARCHAR(10)), '') PERSISTED)

INSERT INTO PROJECTS(PREFIX) VALUES('PID_'),('PID_'),('RID_'),('RID_')

I'm using MS SQL 2012

Upvotes: 4

Views: 9020

Answers (5)

aakash gupta
aakash gupta

Reputation: 1

create table EmployeeMaster ( Row_Id int IDENTITY(1,1) PRIMARY KEY, EmployeeCode AS RIGHT('000' + CAST(Row_Id AS VARCHAR(8)), 3 ) PERSISTED,

Explanation: Row_Id : This column is an IDENTITY column that auto-increments. EmployeeCode: This computed column uses the RIGHT function to ensure that the Row_Id is always displayed with three digits, with leading zeros as necessary. The PERSISTED keyword stores the computed value physically in the table, which can improve performance.

for 1st : EmployeeCode will be 001. For 10th employee, EmployeeCode will be 010. For 100th employee, EmployeeCode will be 100.

Upvotes: 0

Bjorg P
Bjorg P

Reputation: 1073

You can do this with an INSTEAD OF trigger on the table rather than using a PERSISTED column. I have written the trigger so that it will correctly handle bulk inserts as this is something many people overlook. Also, for my solution it is not necessary to have an IDENTITY column on the table if you do not want it.

So the table has been defined with the column included. Also, you can get rid of the IDENTITY column as I mentioned above:

CREATE TABLE dbo.PROJECTS
(
    ID INT IDENTITY(1, 1) NOT NULL,
    PREFIX NVARCHAR(10) NOT NULL,
    PROJECTID NVARCHAR(20) NOT NULL
);

One note - since the PREFIX column is NVARCHAR(10) and I do not know how big the numbers will get, the size of the PROEJCTID column was increased to prevent overflow. Adjust the size as your data requires.

Here is the trigger:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER dbo.InsertProjects
   ON  dbo.PROJECTS
   INSTEAD OF INSERT
AS 
BEGIN
    SET NOCOUNT ON;
    DECLARE @rowsAffected INT = (SELECT COUNT(*) FROM Inserted);
    -- if there are no rows affected, no need to do anything
    IF @rowsAffected = 0 RETURN;

    DECLARE @ExistingCounts TABLE (
        Prefix NVARCHAR(10) NOT NULL, 
        ExistingCount INT NOT NULL
    );

    -- get the count for each existing prefix
    INSERT INTO @ExistingCounts(Prefix, ExistingCount)
    SELECT PREFIX, COUNT(*) FROM dbo.PROJECTS GROUP BY PREFIX;

    -- since this is an INSTEAD OF trigger, we must do the insert ourself.
    -- a prefix might not exist, so use ISNULL() to get a zero in that case.

    INSERT INTO dbo.PROJECTS
    (
        PREFIX, PROJECTID
    )
    SELECT sub.PREFIX, 
        -- the number after the prefix is the existing count for the prefix plus 
        -- the position of the prefix in the Inserted table
        sub.PREFIX + CAST((sub.ExistingCount + sub.Number) AS NVARCHAR(10))
    FROM 
    (SELECT i.PREFIX,
            -- get the position (1, 2, 3...) of the prefix in the Inserted table
           ROW_NUMBER() OVER(PARTITION BY i.PREFIX ORDER BY i.PREFIX) AS [Number], 
           -- get the existing count of the prefix
           ISNULL(c.ExistingCount, 0) AS [ExistingCount]
    FROM Inserted AS i 
    LEFT OUTER JOIN @ExistingCounts AS c ON c.Prefix = i.PREFIX) AS sub;
END
GO

I have included comments in the source code to explain the simple logic. Hopefully this helps and is what you are looking for :-)

Upvotes: 0

Omar
Omar

Reputation: 17

Hi i found the ansowr after working couple of hours in Ms Sql server

USE [StocksDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[tb1_triger]
ON  [dbo].[Table1]
instead of INSERT
AS 
declare @name  nchar(12)
select top 1 @name=name from inserted
declare @maxid char(12)
select @maxid = MAX(id1) from Table1

BEGIN
 SET NOCOUNT ON;
  if (@maxid is null)
  begin
   set @maxid=0
  end
   set @maxid= substring(@maxid,  5 , LEN(@maxid))+1

  INSERT INTO table1 
  (id1,name) SELECT CONCAT_WS((REPLICATE('0',12-4-LEN(@maxid))),'tblo',@maxid),i.name
  from inserted i
  END

Upvotes: 0

navnit
navnit

Reputation: 310

you want like this

CREATE TABLE #PROJECTS
(
  ID INT IDENTITY(1, 1)
         NOT NULL ,
  PREFIX NVARCHAR(10) NOT NULL ,
  PROJECTID NVARCHAR(11)
)


INSERT  INTO #PROJECTS
    ( PREFIX )
VALUES  ( 'PID_' ),
        ( 'PID_' ),
        ( 'RID_' ),
        ( 'RID_' )

suppose you have above data in your table

now if you want to perform insert with DECLARE @PREFIX NVARCHAR(10) = 'RID_'

INSERT  INTO #PROJECTS
    ( PREFIX ,
      PROJECTID
    )
    SELECT  @PREFIX  ,
            @PREFIX  + CAST(( COUNT(TT.rn) + 1 ) AS NVARCHAR(1))
    FROM    ( SELECT    ROW_NUMBER() OVER ( PARTITION BY P.PREFIX ORDER BY ( SELECT
                                                          NULL
                                                          ) ) AS rn
              FROM      #PROJECTS AS P
              WHERE     P.PREFIX = @PREFIX
            ) AS tt

see above query may helps you.

Upvotes: 0

Rajesh Ranjan
Rajesh Ranjan

Reputation: 537

Hey use this query..

                CREATE FUNCTION DBO.GET_NEX_P_ID(@PREF VARCHAR(4))
        RETURNS NVARCHAR(24)
        AS
        BEGIN
        RETURN  (SELECT @PREF+CAST(COUNT(1)+1 AS VARCHAR) FROM PROJECTS WHERE PREFIX=@PREF) 
        END
        GO

        CREATE TABLE PROJECTS
        (
        PREFIX VARCHAR(8),
        PROJECTID NVARCHAR(24)
        )

        GO
        INSERT INTO PROJECTS
        VALUES('PRJ_',DBO.GET_NEX_P_ID('PRJ_'))
        GO
        INSERT INTO PROJECTS
        VALUES('PRQ_',DBO.GET_NEX_P_ID('PRQ_'))
        GO

Thanks

Upvotes: -1

Related Questions