当达到阈值时，如何重置表的标识列？ [英] How do I reset the identity column of a table when a threshold is met?

问题描述

我遗漏了很多我的SQL来巩固我的问题。我有一个临时表声明如下：

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

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

这实质上是将一个日期值（01/01/2002）插入到一个起始ID为0的表中。但是，我需要的是每个月后重置等级。假设1月是我的第一个月，那么我就有：



| ID | CalendarDate |

| 0 | 01/01/2002 |

| 1 | 01/02/2002 |

| 2 | 01/03/2002 |





这对1月很好，但是当输入2月1日时，ID是32而不是0。 br />
关于如何处理这个的任何想法或更好的解决方案？



我尝试过：



我尝试过应用Dense_Rank和Lead功能无济于事。我也试过使用msdn上列出的sum（）方法。

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.

推荐答案

你可以通过执行这个SQL语句来重置标识列：

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)

插入表中的下一条记录将获得您设置的值+1。

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

类似这样的工作对你来说？

Would something like this work for you?

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
;

这篇关于当达到阈值时，如何重置表的标识列？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！