Generate sequence number in SQL on every insert

Question

I need to enter primary key in table as in below format

YYYY/MMM/NNNNNN

Where, YYYY is the current year, MMM is Month , and NNNNNN is a sequence no from 000001, 000002, 000003, .... 999999.

So my primary key will look like 2012/Oct/000001 or 2012/Oct/000010 ....

How can I generate this type of code..

I can get Year and Month from Getdate() function. But how can I manage sequence number on every insert. can you please give me logic for that?

@OlafDietsche getdate is definitely not mysql. T-SQL, I think. — user1726343
– user1726343, Commented Oct 30, 2012 at 9:46
@OlafDietsche I didn't read nawfal's question as a question to you, but to the OP. — user743382
– user743382, Commented Oct 30, 2012 at 9:49

marc_s · Accepted Answer · 2012-10-30 10:28:19Z

4

By far the easiest way would be to let SQL Server handle the dishing out of consecutive numbers using an INT IDENTITY column - and then use a trigger to create that specific format that you need, in a separate column.

So given this table:

CREATE TABLE SampleTable 
             (ID INT IDENTITY, SaleDate DATE, ComputedPK VARCHAR(25) )

you could use a trigger like this to compute the ComputedPK from the ID (autonumber, handled by SQL Server) and the SaleDate as the date column:

CREATE TRIGGER trgSampleTableInsert
ON dbo.SampleTable FOR INSERT
AS 
    UPDATE dbo.SampleTable
    SET ComputedPK = CAST(YEAR(i.SaleDate) AS CHAR(4)) + '/' + DATENAME(MONTH, i.SaleDate) + '/' + RIGHT('000000' + CAST(i.ID AS VARCHAR(6)), 6) 
    FROM dbo.SampleTable s
    INNER JOIN INSERTED i ON s.ID = i.ID

This approach will not start at 1 for each month, however - but do you really need that? Isn't a sequential number (even across months) good enough?

Update: of course, if you're using SQL Server 2012 (you didn't specify which version of SQL Server you're using...) - you could use a SEQUENCE object to handle the consecutive numbering - and you could even reset that sequence to 1 again every start of a month ....

edited Oct 30, 2012 at 10:28

answered Oct 30, 2012 at 9:56

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Damien_The_Unbeliever Over a year ago

Once you've decided to compute it, and the computation is entirely based (as here) on other columns in the same row, any reason you'd prefer to use a trigger here rather than a computed column?

user743382 Over a year ago

I was about to ask exactly the same thing.

ComputedPK AS CAST(YEAR(SaleDate) AS CHAR(4)) + '/' + DATENAME(MONTH, SaleDate) + '/' + RIGHT('000000' + CAST(ID AS VARCHAR(6)), 6)

. It can even be marked as persisted, so that it is stored as were it a regular modifiable field.

marc_s Over a year ago

@Damien_The_Unbeliever: since I cannot persist the computed column (due to the non-deterministic nature of the formula), I felt it was more efficient to compute it in a trigger and store it - rather than having it computed every time you access the column

marc_s Over a year ago

@hvd: actually, I cannot make it PERSISTED:

Msg 4936, Level 16, State 1, Line 1 - Computed column 'ComputedPK' in table 'SampleTable' cannot be persisted because the column is non-deterministic.

- the culprit is the DATENAME function - it's not considered deterministic...

user743382 Over a year ago

@marc_s Oops, that's right, that isn't deterministic, because the month names depend on other data than what's in the table. It should work with a slight modification, but I'll test it first.

|

score 1 · Accepted Answer · 2012-10-30 10:33:30Z

1

SELECT 
CONCAT(
      DATEPART(YEAR, GETDATE()),
      '/',
      DATENAME(MONTH,GETDATE()),
      '/',
      REPLACE(STR(((SELECT COUNT(*) FROM yourtable WHERE monthname = DATENAME(MONTH,GETDATE()) GROUP BY monthname) + 1),6,0),' ','0')
  )
  )

This is ~~untested~~ now tested. You would have to add a monthname column (there is a way of doing this without adding a column, but this is the most convenient)

You can also cast and use addition if you don't want to rely on concat. http://sqlfiddle.com/#!6/3e43d/6

edited Oct 30, 2012 at 10:33

answered Oct 30, 2012 at 9:50

user1726343

2 Comments

marc_s Over a year ago

This will work IF he's using SQL Server 2012 (only - CONCAT is a new feature in the 2012 version)

user1726343 Over a year ago

@marc_s It might not work even then, haven't tested this at all.

Charleh · Accepted Answer · 2012-10-30 09:55:17Z

0

Why don't you just consider a compound key with an identity/numeric column and a date column (or if the day is not needed, the year/month)?

This would give you the same behaviour and would probably be a bit simpler to implement/maintain

I still would like to know the reasoning behind this - just so we can make a better educated guess

answered Oct 30, 2012 at 9:55

Charleh

14k3 gold badges41 silver badges58 bronze badges

Comments

SchmitzIT · Accepted Answer · 2012-10-30 10:31:58Z

0

Set your Primary Key on two columns. One of them will represent the Daten and the other one the Serial number. Set a Sequence on the Serial column which increases automatically. This will make it easier for you filter the Date part of the Key.

edited Oct 30, 2012 at 10:31

SchmitzIT

9,74912 gold badges71 silver badges96 bronze badges

answered Oct 30, 2012 at 9:47

CloudyMarble

37.7k70 gold badges103 silver badges133 bronze badges

3 Comments

Paul Alan Taylor Over a year ago

That doesn't cover him when he switches months, though.

user743382 Over a year ago

I'm assuming the sequence should be per month, so after all the entries for October, you'd get 2012/Nov/000001. Your solution won't do this.

CloudyMarble Over a year ago

The OP didnt mention this should be the case.

Collectives™ on Stack Overflow

Generate sequence number in SQL on every insert

4 Answers 4

6 Comments

2 Comments

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

6 Comments

2 Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related