locked
How to auto generate unique varchar value while inserting data into table RRS feed

  • Question

  • Hii

    How to auto generate a primary key varchar value while inserting data into a table in sql server 2008...

    Please help me to know.....

    Thanks in advance...

    Saturday, January 5, 2013 4:22 AM

Answers

  • If you are using an auto-generated value as your primary key, is there a particular reason you need a varchar data type as opposed to a different type, such as uniqueidentifier?  Using a uniqueidentifier, you can define a default value in the table definition (DEFAULT NEWID()) to ensure a unique value.  Here is more info on uniqueidentifier:

    http://msdn.microsoft.com/en-us/library/ms190215(v=SQL.105).aspx

    If you need to use a varchar, one potential option would be to use a uniqueidentifier data type cast to a varchar(36).  

    Thanks,
    Sam Lester (MSFT)


    My Blog

    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

    • Marked as answer by Kalman Toth Tuesday, January 15, 2013 9:14 PM
    Saturday, January 5, 2013 4:37 AM
  • I did it by using below procedure...

    CREATE PROCEDURE SP_INSERT  
    @Employeeid varchar(max),  
    @RequestDate datetime,  
    @FromDate datetime,  
    @ToDate datetime,  
    @FromTime time,  
    @ToTime time,  
    @UpdatedBy varchar(max),  
    @UpdatedTime datetime,  
    @StatusID int,  
    @LeaveCode varchar(10)  

    AS  
    BEGIN  
     /* Logic for Getting New ID as Per the NAME with PRE FIX */  
     DECLARE @NEWID VARCHAR(10);  
     DECLARE @PREFIX VARCHAR(10);  
     Declare @lms varchar(5);  
    set @lms='LMS';  
     SET @PREFIX =@lms+(SUBSTRING(@Employeeid,1,1))  
     SELECT @NEWID = (@PREFIX + replicate('0', 3 - len(CONVERT(VARCHAR,N.OID + 1))) + CONVERT(VARCHAR,N.OID + 1)) FROM (  
      SELECT CASE WHEN MAX(T.TID) IS null then 0 else MAX(T.TID) end as OID FROM (  
       SELECT SUBSTRING(RequestID,1,4) as PRE_FIX,SUBSTRING(RequestID,5,LEN(RequestID)) as TID FROM LeaveRequest  
      ) AS T WHERE T.PRE_FIX = @PREFIX  
       ) AS N  
     /* INSERT QUERY FOR NEW RECORD */  
     INSERT INTO LeaveRequest VALUES (@NEWID,@Employeeid,@RequestDate,  
    @FromDate,  
    @ToDate,  
    @FromTime,  
    @ToTime,  
    @UpdatedBy,  
    @UpdatedTime,  
    @StatusID,  
    @LeaveCode)  
    END  

       Thanks for your replies.........
    • Marked as answer by Sanam K M N Monday, May 27, 2013 9:00 AM
    • Edited by Sanam K M N Monday, May 27, 2013 9:01 AM
    Monday, May 27, 2013 9:00 AM

All replies

  • If you are using an auto-generated value as your primary key, is there a particular reason you need a varchar data type as opposed to a different type, such as uniqueidentifier?  Using a uniqueidentifier, you can define a default value in the table definition (DEFAULT NEWID()) to ensure a unique value.  Here is more info on uniqueidentifier:

    http://msdn.microsoft.com/en-us/library/ms190215(v=SQL.105).aspx

    If you need to use a varchar, one potential option would be to use a uniqueidentifier data type cast to a varchar(36).  

    Thanks,
    Sam Lester (MSFT)


    My Blog

    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

    • Marked as answer by Kalman Toth Tuesday, January 15, 2013 9:14 PM
    Saturday, January 5, 2013 4:37 AM
  • I need to generate id as LMS0111

    Here LMS is common for all records and 01 is particular employeeid and 11 must be incremented automatically..

    How to do this...???

    Saturday, January 5, 2013 4:41 AM
  • Try with Computed column -

    1.

    Using Computed column

    2.

    http://stackoverflow.com/questions/2177584/sqlserver-identity-column-with-text 



    Narsimha

    • Proposed as answer by Kalman Toth Saturday, January 5, 2013 8:37 AM
    • Marked as answer by Maggie Luo Monday, January 14, 2013 2:36 PM
    • Unmarked as answer by Sanam K M N Monday, May 27, 2013 8:58 AM
    Saturday, January 5, 2013 6:49 AM
  • You can use code like the following to accomplish this

    CREATE TABLE dbo.Test(a int IDENTITY, b AS  'LMS' + CAST(a AS CHAR(3))) 

    INSERT INTO Test DEFAULT VALUES

    2. Test whether the values are proper

    SELECT * FROM dbo.Test

    You would need to expand this to include employeeid for your requirement

    HTH

    Veera

     
    • Proposed as answer by Veerendra Thati Saturday, January 5, 2013 8:12 PM
    • Marked as answer by Maggie Luo Monday, January 14, 2013 2:36 PM
    • Unmarked as answer by Sanam K M N Monday, May 27, 2013 8:58 AM
    Saturday, January 5, 2013 8:12 PM
  • You will need to do this in code - there is nothing built into sql server that will accomodate your need (including the use of the empoyeeID).  Sequences might be of use - but that is in 2012.  One thing you need to consider is the effect of deleting rows on the values in the remaining rows as well as the value of any new rows after deletion.
    • Proposed as answer by Naomi NEditor Monday, January 7, 2013 6:18 PM
    • Marked as answer by Maggie Luo Monday, January 14, 2013 2:36 PM
    • Unmarked as answer by Sanam K M N Monday, May 27, 2013 8:58 AM
    Monday, January 7, 2013 2:39 PM
  • One way (though not really the best performer) would be to use cast(newid() as char(36)) in a default constraint. Ugly, but possible.

    Veerenda's idea of using the identity value and then casting as an integer in a persisted computed column is where I would go for 2008 too.

    In 2012 (if you could upgrade, which I know is probabaly out of the picture), I would suggest using a SEQUENCE object and in the default constraint ... Gives you a lot of flexibility on how to get the next value.

    Code:

    IF EXISTS (SELECT * FROM sys.tables where object_id = OBJECT_ID('Demo.NonIntegerColumn'))
      DROP TABLE Demo.NonIntegerColumn;
    GO
    IF EXISTS (SELECT * FROM sys.sequences where object_id = OBJECT_ID('Demo.NonIntegerColumn_SEQUENCE'))
      DROP SEQUENCE Demo.NonIntegerColumn_SEQUENCE;
    GO
    CREATE SEQUENCE Demo.NonIntegerColumn_SEQUENCE
    AS int
    START WITH 1
    GO
    CREATE TABLE Demo.NonIntegerColumn
    (
     HexKey varchar(8) PRIMARY KEY
        DEFAULT (SUBSTRING(CONVERT(varchar(10), cast(NEXT VALUE FOR Demo.NonIntegerColumn_SEQUENCE as varbinary(10)), 1),3,10)),
     Value int
    )
    GO


    Louis

    Without good requirements, my advice is only guesses. Please don't hold it against me if my answer answers my interpretation of your questions.

    • Proposed as answer by Kalman Toth Tuesday, January 15, 2013 9:16 PM
    Tuesday, January 15, 2013 7:51 PM
  • >How to auto generate a primary key varchar value

    Varchar columns is not good for PRIMARY KEY for several reasons. Make it into a UNIQUE KEY and use INT IDENTITY as surrogate PRIMARY KEY.


    Kalman Toth SQL 2008 GRAND SLAM
    Paperback: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

    Tuesday, January 15, 2013 9:15 PM
  • Hi I have a result and I need to add a new field with auto incrementation in the new field

    I need the result set something like

    zzz10000abc

    zzz10001abc

    zzz10002abc

    zzz10003abc and so on plz help...

    • Merged by Kalman Toth Tuesday, February 5, 2013 6:12 PM Duplicate
    Monday, January 28, 2013 5:03 PM
  • If you are using SQL Server 2012 you can use either an identity column, a sequential guid or a sequence object.  For versions before SQL 2012 you can use either either an identity column or a sequential guid.  Also, I suggest that what you are proposing be a computed column because this column is otherwise not "atomic".
    Monday, January 28, 2013 5:09 PM
  • one way ..

    BEGIN TRAN
    
    CREATE TABLE dbo.A 
    (
        intID int IDENTITY(10001, 1) NOT NULL
        ,A varchar(10)
        ,X as 'zzz' + CAST(intID as nvarchar(10)) + 'abc'
    )
    
    INSERT dbo.A (A) VALUES ('A'), ('B')
    
    SELECT * FROM dbo.A
    
    
    ROLLBACK
    


    Vladimir Moldovanenko

    Monday, January 28, 2013 5:12 PM
  • SQL is not ideal for this, but there are a few options if you must use it.

    Firstly, I would recommend splitting the resultset into its constituent parts, leaving your incremental number as its own field.

    1. If you're using SQL 2012 you can use the LAG function to get the previous value and increment by 1
    2. You could store the data in a temp table and use an identity field to auto generate the incremental number (although an identity field does not guarantee the increment as gaps can form due to errored inserts)
    3. Use a different window function such as ROW_NUMBER() to generate an incremental number.

    Finally, you can use a computed column, or simply a query, to concatenate the parts back together and produce your target resultset.

    Monday, January 28, 2013 5:13 PM
  • There's a similar thread recently on this -

    http://social.msdn.microsoft.com/Forums/en-US/databasedesign/thread/f1466c37-6546-4344-823f-5a32b99ce2b2


    Narsimha

    Monday, January 28, 2013 5:42 PM
  • I did it by using below procedure...

    CREATE PROCEDURE SP_INSERT  
    @Employeeid varchar(max),  
    @RequestDate datetime,  
    @FromDate datetime,  
    @ToDate datetime,  
    @FromTime time,  
    @ToTime time,  
    @UpdatedBy varchar(max),  
    @UpdatedTime datetime,  
    @StatusID int,  
    @LeaveCode varchar(10)  

    AS  
    BEGIN  
     /* Logic for Getting New ID as Per the NAME with PRE FIX */  
     DECLARE @NEWID VARCHAR(10);  
     DECLARE @PREFIX VARCHAR(10);  
     Declare @lms varchar(5);  
    set @lms='LMS';  
     SET @PREFIX =@lms+(SUBSTRING(@Employeeid,1,1))  
     SELECT @NEWID = (@PREFIX + replicate('0', 3 - len(CONVERT(VARCHAR,N.OID + 1))) + CONVERT(VARCHAR,N.OID + 1)) FROM (  
      SELECT CASE WHEN MAX(T.TID) IS null then 0 else MAX(T.TID) end as OID FROM (  
       SELECT SUBSTRING(RequestID,1,4) as PRE_FIX,SUBSTRING(RequestID,5,LEN(RequestID)) as TID FROM LeaveRequest  
      ) AS T WHERE T.PRE_FIX = @PREFIX  
       ) AS N  
     /* INSERT QUERY FOR NEW RECORD */  
     INSERT INTO LeaveRequest VALUES (@NEWID,@Employeeid,@RequestDate,  
    @FromDate,  
    @ToDate,  
    @FromTime,  
    @ToTime,  
    @UpdatedBy,  
    @UpdatedTime,  
    @StatusID,  
    @LeaveCode)  
    END  

       Thanks for your replies.........
    • Marked as answer by Sanam K M N Monday, May 27, 2013 9:00 AM
    • Edited by Sanam K M N Monday, May 27, 2013 9:01 AM
    Monday, May 27, 2013 9:00 AM