How do I reset the identity column of a table when a threshold is met?

Question

0.00/5 (No votes)

See more:

I'm leaving out much of my sql to consolidate my question. I have a temp table declared as follows:

SQL

if OBJECT_ID('Tempdb..#RankCalendar','U') is not null
	drop table #RankCalendar 

CREATE TABLE #RankCalendar
(ID INT identity(0,1) 
,CalendarDate DATE
)
INSERT INTO #RankCalendar
SELECT 
 cal.CalendarDate
FROM tblCalendar cal

This essentially inserts a date value (01/01/2002) into a table with a starting ID of 0. What I need however, is for the rank to reset after every month. So assuming January is my first month, then I'd have:

| ID | CalendarDate |
| 0 | 01/01/2002 |
| 1 | 01/02/2002 |
| 2 | 01/03/2002 |

This is great for January but when February 1st is entered the ID is 32 rather than 0.
Any ideas or better yet solutions on how to handle this?

What I have tried:

I've tried applying Dense_Rank and Lead functions to no avail. I've also tried to use the sum over() approach listed on msdn.

Posted 3-Aug-16 8:47am

Member 11820531

Updated 3-Aug-16 10:17am

Add a Solution

Comments

Richard Deeming 3-Aug-16 16:14pm

An identity column is the wrong approach. It's not intended to be reset for anything other than manual maintenance tasks.

What problems did you have using DENSE_RANK?

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Dave Kreskowiak · Answer 1 · 2016-08-03T09:00:00

Solution 1

You can "reset" the identity column by executing this SQL statement:

DBCC CHECKIDENT ('tableNameGoesHere')

If you want to reseed the column to a known value, you can use:

DBCC CHECKIDENT ('tableNameGoesHere', RESEED, value)

The next record inserted into the table will get whatever the value is you set +1.

Posted 3-Aug-16 9:00am

Dave Kreskowiak

Comments

Member 11820531 3-Aug-16 15:09pm

That's more of a "after the fact approach". Ideally, I'd like to control re-seeding when my temp table is created and populated. I believe your solution is tied more towards a "permenent" table. Thanks for the info though!

Richard Deeming · Answer 2 · 2016-08-03T10:17:00

Solution 2

Would something like this work for you?

SQL

if OBJECT_ID('Tempdb..#RankCalendar','U') is not null
	drop table #RankCalendar;
 
CREATE TABLE #RankCalendar
(
    ID INT NOT NULL, 
    CalendarDate DATE NOT NULL
);

INSERT INTO #RankCalendar
(
    ID,
    CalendarDate
)
SELECT 
    DENSE_RANK() OVER (PARTITION BY EOMONTH(cal.CalendarDate) ORDER BY cal.CalendarDate) - 1,
    cal.CalendarDate
FROM 
    tblCalendar cal
;

Posted 3-Aug-16 10:17am

Richard Deeming

Comments

Member 11820531 3-Aug-16 17:46pm

EOMONTH is not recognized as a built in function on my DB. I'd have to change my database compatibility level. The solution for my problem was that I didn't make my query more selective in its creation. Thank you for your info! In hindsight, my question didn't fully encapsulate what I was trying to accomplish. So I didn't ask the correct question. Thanks again.