September 12, 2013 at 1:15 am
Hi,
I want to reset an Identity Column 1 Time in Month (by T-SQL -not job).
How can I make sure that this action is performed only 1 Times every Month.
Like this...
if datepart(month,getdate()) != datepart(month,dateFromTableValue)
Truncate Table
DBCC CHECKIDENT('dbo.MyTable', RESEED, 1);
Regards
Nicole
😉
September 12, 2013 at 3:05 am
If you want to run this every month, you'd need some sort of scheduling.
By the way, if you truncate the table it is already reseeded, so you don't need the DBCC command.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
September 12, 2013 at 3:37 am
I don't think its a good idea to reset Identity column every month..rather then that you can truncate the table every month so that Identity gets reseed atuomatically....
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
September 12, 2013 at 7:38 am
There really isn't a great way of making sure that it happens only once a month. Like the others have said, truncate does the reseed automatically. I can think of 1 way to try to make sure it only happens once a month:
Create a table called ReseedCheck or something with 2 columns tableName and reseedDate. Then put your reseed/truncate code in a stored procedure that does something like this:
IF NOT EXISTS ( SELECT
1
FROM
maintenance.reseedCheck
WHERE
tableName = '
' AND
reseedDate >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) AND
reseedDate < DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0) )
BEGIN;
TRUNCATE TABLE
;
INSERT INTO maintenance.reseedCheck
(tableName, reseedDate)
VALUES
('
', GETDATE());
END;
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply