locked
How to create auto increment value in my column using identity ? RRS feed

  • Question

  • hi Team,

    I have an requirement where i create an auto increment value ,with my table column

    Create table Temp(

    DeptID int IDENTITY(1,1) PRIMARY KEY,

    Name varchar(50),

    Emailid nvarchar(50),

    Phone varchar(50)

    )

    so this is my table structure ,Here my column name is

    Deptid here i need to creat an autoincrement value with today's date like below

    ex:STM0000120012015

        STM0000221012015

        STM0000322012015(Currentdate)

    .......................................... like this

    Here i need  only one column like identity column with the given incremental order,not more than one column

    so can u pls help me out any one.

    Thanks!




    • Edited by Ychinnari Wednesday, January 21, 2015 6:56 AM
    Tuesday, January 20, 2015 12:46 PM

Answers

  • CREATE TABLE #t (id  INT  NOT NULL IDENTITY(1,1) , 
    col AS 'STM0000'+ CAST(id AS VARCHAR(20))+'20012015')

    INSERT INTO #t DEFAULT VALUES 
    GO 10

    SELECT * FROM #t

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Marked as answer by Ychinnari Wednesday, January 21, 2015 8:41 AM
    Tuesday, January 20, 2015 1:16 PM
  • Try the below:

    CREATE TABLE #t (id  INT  NOT NULL IDENTITY(1,1) , 
    col AS 'STM'+ Right('0000'+CAST(id AS VARCHAR(20)),5)+Replace(CONVERT(VARCHAR(10), GETDATE(), 104),'.',''))
    Go
    INSERT INTO #t DEFAULT VALUES 
    GO 20
    
    SELECT * FROM #t
    
    Drop table #t
    

    • Marked as answer by Ychinnari Wednesday, January 21, 2015 8:40 AM
    Wednesday, January 21, 2015 7:28 AM
    Answerer

All replies

  • CREATE TABLE #t (id  INT  NOT NULL IDENTITY(1,1) , 
    col AS 'STM0000'+ CAST(id AS VARCHAR(20))+'20012015')

    INSERT INTO #t DEFAULT VALUES 
    GO 10

    SELECT * FROM #t

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Marked as answer by Ychinnari Wednesday, January 21, 2015 8:41 AM
    Tuesday, January 20, 2015 1:16 PM
  • You can create a computed column for this purpose. However it will be difficult to create a primary key out of this with a increment number specific for day and even if you are able to create one there could be performance implications for such a design. 

    Could you explain the rationale behind such a logic for primary key?



    Satheesh
    My Blog | How to ask questions in technical forum

    Tuesday, January 20, 2015 2:10 PM
  • Can you please update the above query for table creation,

    Here i need only one column like below

    STM0000120012015(CurrentDate)

    i.e STM+00001+Getdate() this format i need in my primaykey column

    So can you pls update it now

    Wednesday, January 21, 2015 6:59 AM
  • >>STM0000120012015(CurrentDate)

    What happen when the date changes?  Is it a running number? which is primary key formation you would expect ?

    STM0000120012015
    STM0000220012015
    STM0000320012015
    STM0000121012015
    STM0000221012015
    
    or 
    STM0000120012015
    STM0000220012015
    STM0000320012015
    STM0000421012015
    STM0000521012015
    


    Satheesh
    My Blog | How to ask questions in technical forum

    Wednesday, January 21, 2015 7:18 AM
  • MY requirement is that only,

    so i dnt need two fileds, i need one column like above i have suggested,

    So can you pls give me for the above requested query

    Wednesday, January 21, 2015 7:24 AM
  • Try the below:

    CREATE TABLE #t (id  INT  NOT NULL IDENTITY(1,1) , 
    col AS 'STM'+ Right('0000'+CAST(id AS VARCHAR(20)),5)+Replace(CONVERT(VARCHAR(10), GETDATE(), 104),'.',''))
    Go
    INSERT INTO #t DEFAULT VALUES 
    GO 20
    
    SELECT * FROM #t
    
    Drop table #t
    

    • Marked as answer by Ychinnari Wednesday, January 21, 2015 8:40 AM
    Wednesday, January 21, 2015 7:28 AM
    Answerer

  • so i dnt need two fileds, i need one column like above i have suggested,


    It does not matter as long as you can use the computed column for your display purpose. If you need only one column, then, you may get the MAX() of the current value everytime you need to insert a reocrds, which requires a table scan, hence, a performance issue. 
    Wednesday, January 21, 2015 7:30 AM
    Answerer
  • MY requirement is that only,

    so i dnt need two fileds, i need one column like above i have suggested,

    So can you pls give me for the above requested query

    I think you didn't understand my question, Do you want the sequence number to be reset when the date changes?( As I shown in my previous post)

    Satheesh
    My Blog | How to ask questions in technical forum

    Wednesday, January 21, 2015 7:30 AM
  • Here the output came like this ,

    1 STM0000120150121
    2 STM0000220150121
    3 STM0000320150121
    4 STM0000420150121
    5 STM0000520150121
    6 STM0000620150121
    7 STM0000720150121
    8 STM0000820150121
    9 STM0000920150121
    10 STM00001020150121 --see this exceed length

    and here i dnt need to increment that Stm000010,Here my output will come like this, idnt need to increment my charcter size

    1 STM0000120150121
    2 STM0000220150121
    3 STM0000320150121
    4 STM0000420150121
    5 STM0000520150121
    6 STM0000620150121
    7 STM0000720150121
    8 STM0000820150121
    9 STM0000920150121
    10 STM0001020150121
    11    STM0001120150121
    12    STM0001220150121 

    so here i dont  need to increment my charcter length(16)

    The length should be STM(3char)+00001(5Charcters)+CurrentDateFormat,

    see the above suggested o/p

    so can u pls help me out Dimant


    • Edited by Ychinnari Wednesday, January 21, 2015 7:37 AM
    Wednesday, January 21, 2015 7:36 AM
  • You would need two columns (one Identity and one primary key column itself)to generate such a Primary key. Its not possible otherwise(technically its possible, but have performance implication as stated earlier)

    Satheesh
    My Blog | How to ask questions in technical forum

    Wednesday, January 21, 2015 7:48 AM