How to Reseed an Identity Column on Temp Table After data has been removed

Question

How to Reseed an Identity Column on Temp Table After data has been removed

Weegee71

SSChasing Mays

Points: 604
More actions
January 23, 2023 at 12:44 pm

#4140759

Hi there
We have a temp table in our code that contains an Identity column. Now data will get removed from this table and I want to reorder the ID Column sequentially , so that ID appears as 1,2,3 , 4 etc...
/****** Table and Data re-creation script ***********************************************************************************************************************************************/
DROP TABLE IF EXISTS [#zzitems]
CREATE TABLE [#zzitems](
[ID] INT IDENTITY(1,1) Not null,
[DeviceID] [uniqueidentifier] NULL,
[ChannelID] [nvarchar](10) NULL,
[SerialNumber] [nvarchar](10) NULL,
[DeviceChannelID] [uniqueidentifier] NULL,
[ReadingDownloadName] [varchar](100) NULL,
[ReadingStartDate] [datetime2](7) NULL,
[ReadingEndDate] [datetime2](7) NULL,
[Amount] [int] NULL,
[Sortorder] [nvarchar](30) NULL,
[IncludeFirstDate] [int] NULL,
[DownloadTableName] [nvarchar](100) NULL
) ON [PRIMARY]
GO
INSERT [#zzitems] ( [DeviceID], [ChannelID], [SerialNumber], [DeviceChannelID], [ReadingDownloadName], [ReadingStartDate], [ReadingEndDate], [Amount], [Sortorder], [IncludeFirstDate], [DownloadTableName]) VALUES ( N'264ab60e-104f-42da-888b-b93a368c9ce4', N'16', N'926371', N'cd278863-7e85-42b1-81a6-af50c1633cb4', N'ChannelReading_Dummy4A03C53F-B63B-4721-A921-DE0C2316065F', CAST(N'2022-05-20T13:49:08.0000000' AS DateTime2), CAST(N'2023-01-23T11:05:33.0000000' AS DateTime2), 50, N'Ascending', 1, N'ChannelReading_926371_16_CD278863-7E85-42B1-81A6-AF50C1633CB4')
GO
INSERT [#zzitems] ( [DeviceID], [ChannelID], [SerialNumber], [DeviceChannelID], [ReadingDownloadName], [ReadingStartDate], [ReadingEndDate], [Amount], [Sortorder], [IncludeFirstDate], [DownloadTableName]) VALUES ( N'264ab60e-104f-42da-888b-b93a368c9ce4', N'12', N'926371', N'2a775060-d371-4929-ba17-e119cf7cd2a8', N'ChannelReading_Dummy4A03C53F-B63B-4721-A921-DE0C2316065F', CAST(N'2022-05-20T13:49:08.0000000' AS DateTime2), CAST(N'2023-01-23T11:05:33.0000000' AS DateTime2), 50, N'Ascending', 1, N'ChannelReading_926371_12_2A775060-D371-4929-BA17-E119CF7CD2A8')
GO
INSERT [#zzitems] ( [DeviceID], [ChannelID], [SerialNumber], [DeviceChannelID], [ReadingDownloadName], [ReadingStartDate], [ReadingEndDate], [Amount], [Sortorder], [IncludeFirstDate], [DownloadTableName]) VALUES ( N'264ab60e-104f-42da-888b-b93a368c9ce4', N'15', N'926371', N'9de4017b-6bb4-4d1f-a09e-e7fafaaca8a8', N'ChannelReading_Dummy4A03C53F-B63B-4721-A921-DE0C2316065F', CAST(N'2022-05-20T13:49:08.0000000' AS DateTime2), CAST(N'2023-01-23T11:05:33.0000000' AS DateTime2), 50, N'Ascending', 1, N'ChannelReading_926371_15_9DE4017B-6BB4-4D1F-A09E-E7FAFAACA8A8')
GO
INSERT [#zzitems] ( [DeviceID], [ChannelID], [SerialNumber], [DeviceChannelID], [ReadingDownloadName], [ReadingStartDate], [ReadingEndDate], [Amount], [Sortorder], [IncludeFirstDate], [DownloadTableName]) VALUES ( N'264ab60e-104f-42da-888b-b93a368c9ce4', N'17', N'926371', N'cd278863-7e85-42b1-81a6-af50c1633cb4', N'ChannelReading_Dummy4A03C53F-B63B-4721-A921-DE0C2316065F', CAST(N'2022-05-20T13:49:08.0000000' AS DateTime2), CAST(N'2023-01-23T11:05:33.0000000' AS DateTime2), 50, N'Ascending', 1, N'ChannelReading_926371_16_CD278863-7E85-42B1-81A6-AF50C1633CB4')
GO
INSERT [#zzitems] ( [DeviceID], [ChannelID], [SerialNumber], [DeviceChannelID], [ReadingDownloadName], [ReadingStartDate], [ReadingEndDate], [Amount], [Sortorder], [IncludeFirstDate], [DownloadTableName]) VALUES ( N'264ab60e-104f-42da-888b-b93a368c9ce4', N'18', N'926371', N'2a775060-d371-4929-ba17-e119cf7cd2a8', N'ChannelReading_Dummy4A03C53F-B63B-4721-A921-DE0C2316065F', CAST(N'2022-05-20T13:49:08.0000000' AS DateTime2), CAST(N'2023-01-23T11:05:33.0000000' AS DateTime2), 50, N'Ascending', 1, N'ChannelReading_926371_12_2A775060-D371-4929-BA17-E119CF7CD2A8')
GO
INSERT [#zzitems] ( [DeviceID], [ChannelID], [SerialNumber], [DeviceChannelID], [ReadingDownloadName], [ReadingStartDate], [ReadingEndDate], [Amount], [Sortorder], [IncludeFirstDate], [DownloadTableName]) VALUES ( N'264ab60e-104f-42da-888b-b93a368c9ce4', N'15', N'926371', N'9de4017b-6bb4-4d1f-a09e-e7fafaaca8a8', N'ChannelReading_Dummy4A03C53F-B63B-4721-A921-DE0C2316065F', CAST(N'2022-05-20T13:49:08.0000000' AS DateTime2), CAST(N'2023-01-23T11:05:33.0000000' AS DateTime2), 50, N'Ascending', 1, N'ChannelReading_926371_15_9DE4017B-6BB4-4D1F-A09E-E7FAFAACA8A8')
GO

delete from [#zzitems] where id in (1,3,5)
SELECT * FROM [#zzitems]
DBCC CHECKIDENT('#zzitems', RESEED, 1)
/****** Table and Data re-creation script ************************************************************************************************************************
I tried using the following
DBCC CHECKIDENT('#zzitems', RESEED, 1) but that did not have an effect and did not re-set the ID
i then tried to change the ID column to a simple INT and then insert data using Row_Number
Now that worked fine. However when i went to try and update the ID column using the following:
Update i
set ID = row_number() OVER ( order by DeviceID, DeviceChannelID, ReadingStartDate )
from #items i
I got the following message
Msg 4108, Level 15, State 1, Line 284
Windowed functions can only appear in the SELECT or ORDER BY clauses
How can i re-set the ID in this temp table after I have removed records?
See screenshot attached

Attachments:
You must be logged in to view attached files.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99130 More actions · Answer 1

Why do you want to reseed the value? You only DELETE the rows with a ID of 1, 3, and 5. If you reseed the IDENTITY (so that the next row has 1, presumably), then when you INSERT another row you would end up with 2 rows with a ID of 2; is that desired?

So, speaking hypothetically, you inserted the data you do above, and then deleted the same rows. If you reseed the IDENTITY so that the next row gets an ID of 1, and you then INSERTed 6 new rows, you would have 1 row for the IDs 1, 3, and 5, however, there would be 2 rows for the IDs 2, 4, and 6.

This reply was modified 1 year, 10 months ago by Thom A.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Weegee71 SSChasing Mays Points: 604 More actions · Answer 2

I want to reseed the Value as Im then processing values using a loop , sequentially steping through the records

So i have something like this

SELECT

@VinCounter = MIN([ID]),

@VinMaxCounter = MAX([ID])

FROM

[#Items]

WHILE @VinCounter <= @VinMaxCounter

BEGIN

--- process data

SET @VinCounter = @VinCounter + 1

END

Now after deleting records prior to this, if my ID values are as follows:

ID

2

3

5

Then this will get called 4 times.. instead of 3 times (for 3 records) as its stepping though sequentially

Thom A SSC Guru Points: 99130 More actions · Answer 3

The fact that you're using a loop sounds like there are other problems too. T-SQL is a set-based language and so excels at set-based solutions. Conversely, it doesn't perform well at iterative tasks.

Honestly, I think you might be better off taking a step back and explaining the whole problem you're trying to solve, rather than the problem you explain here about reseeding the identity.

Also, if you are posting code, please do pop it in a code box; it makes it much easier for us to read. Thanks!

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Phil Parkin SSC Guru Points: 246960 More actions · Answer 4

As you are using row-by-row processing, why not use a CURSOR?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

ScottPletcher SSC Guru Points: 101098 More actions · Answer 5

You cannot update the value of an identity column. You would to insert a new row and delete the old row. That will be way too much overhead.

And, as Thom noted, it's a bad idea anyway. But, if you must insist on using a loop, have the loop find the next value rather than just adding 1.

WHILE @VinCounter <= @VinMaxCounter

BEGIN

SELECT @VinCounter = MIN(ID)

FROM [#items]

WHERE ID >= @VinCounter

...

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Michael L John One Orange Chip Points: 27055 More actions · Answer 6

I'm with Thom. Show us the rest of the query.

Even if a loop is used, why do is there a requirement for consecutive numbers? Instead of incrementing @VinCounter, query for the next one that's greater than the previous one.

Which is probably worse than a cursor as Phil suggested.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 7

If the looping is necessary, just add a column (maybe call it ProcessOrder) to the temp table and populate it with ROW_NUMBER() OVER (ORDER BY ID).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 8

You cannot update the value of the identity column in SQL Server using UPDATE statement. You can delete the existing column and re-insert it with a new identity value. The only way to remove the identity property for the column is by removing the identity column itself. A quick way to change the identity column for lots of rows is to inset them into a temporary table, truncate the original table then reinsert them:

/****** Table and Data re-creation script ***********************************************************************************************************************************************/DROP TABLE IF EXISTS #zzitems

CREATE TABLE #zzitems
(
    [ID] INT IDENTITY(1,1) Not null,
    [DeviceID] [uniqueidentifier] NULL,
    [ChannelID] [nvarchar](10) NULL,
    [SerialNumber] [nvarchar](10) NULL,
    [DeviceChannelID] [uniqueidentifier] NULL,
    [ReadingDownloadName] [varchar](100) NULL,
    [ReadingStartDate] [datetime2](7) NULL,
    [ReadingEndDate] [datetime2](7) NULL,
    [Amount] [int] NULL,
    [Sortorder] [nvarchar](30) NULL,
    [IncludeFirstDate] [int] NULL,
    [DownloadTableName] [nvarchar](100) NULL
) 

GO
INSERT [#zzitems] ( [DeviceID], [ChannelID], [SerialNumber], [DeviceChannelID], [ReadingDownloadName], [ReadingStartDate], [ReadingEndDate], [Amount], [Sortorder], [IncludeFirstDate], [DownloadTableName]) VALUES ( N'264ab60e-104f-42da-888b-b93a368c9ce4', N'16', N'926371', N'cd278863-7e85-42b1-81a6-af50c1633cb4', N'ChannelReading_Dummy4A03C53F-B63B-4721-A921-DE0C2316065F', CAST(N'2022-05-20T13:49:08.0000000' AS DateTime2), CAST(N'2023-01-23T11:05:33.0000000' AS DateTime2), 50, N'Ascending', 1, N'ChannelReading_926371_16_CD278863-7E85-42B1-81A6-AF50C1633CB4')
INSERT [#zzitems] ( [DeviceID], [ChannelID], [SerialNumber], [DeviceChannelID], [ReadingDownloadName], [ReadingStartDate], [ReadingEndDate], [Amount], [Sortorder], [IncludeFirstDate], [DownloadTableName]) VALUES ( N'264ab60e-104f-42da-888b-b93a368c9ce4', N'12', N'926371', N'2a775060-d371-4929-ba17-e119cf7cd2a8', N'ChannelReading_Dummy4A03C53F-B63B-4721-A921-DE0C2316065F', CAST(N'2022-05-20T13:49:08.0000000' AS DateTime2), CAST(N'2023-01-23T11:05:33.0000000' AS DateTime2), 50, N'Ascending', 1, N'ChannelReading_926371_12_2A775060-D371-4929-BA17-E119CF7CD2A8')
INSERT [#zzitems] ( [DeviceID], [ChannelID], [SerialNumber], [DeviceChannelID], [ReadingDownloadName], [ReadingStartDate], [ReadingEndDate], [Amount], [Sortorder], [IncludeFirstDate], [DownloadTableName]) VALUES ( N'264ab60e-104f-42da-888b-b93a368c9ce4', N'15', N'926371', N'9de4017b-6bb4-4d1f-a09e-e7fafaaca8a8', N'ChannelReading_Dummy4A03C53F-B63B-4721-A921-DE0C2316065F', CAST(N'2022-05-20T13:49:08.0000000' AS DateTime2), CAST(N'2023-01-23T11:05:33.0000000' AS DateTime2), 50, N'Ascending', 1, N'ChannelReading_926371_15_9DE4017B-6BB4-4D1F-A09E-E7FAFAACA8A8')
INSERT [#zzitems] ( [DeviceID], [ChannelID], [SerialNumber], [DeviceChannelID], [ReadingDownloadName], [ReadingStartDate], [ReadingEndDate], [Amount], [Sortorder], [IncludeFirstDate], [DownloadTableName]) VALUES ( N'264ab60e-104f-42da-888b-b93a368c9ce4', N'17', N'926371', N'cd278863-7e85-42b1-81a6-af50c1633cb4', N'ChannelReading_Dummy4A03C53F-B63B-4721-A921-DE0C2316065F', CAST(N'2022-05-20T13:49:08.0000000' AS DateTime2), CAST(N'2023-01-23T11:05:33.0000000' AS DateTime2), 50, N'Ascending', 1, N'ChannelReading_926371_16_CD278863-7E85-42B1-81A6-AF50C1633CB4')
INSERT [#zzitems] ( [DeviceID], [ChannelID], [SerialNumber], [DeviceChannelID], [ReadingDownloadName], [ReadingStartDate], [ReadingEndDate], [Amount], [Sortorder], [IncludeFirstDate], [DownloadTableName]) VALUES ( N'264ab60e-104f-42da-888b-b93a368c9ce4', N'18', N'926371', N'2a775060-d371-4929-ba17-e119cf7cd2a8', N'ChannelReading_Dummy4A03C53F-B63B-4721-A921-DE0C2316065F', CAST(N'2022-05-20T13:49:08.0000000' AS DateTime2), CAST(N'2023-01-23T11:05:33.0000000' AS DateTime2), 50, N'Ascending', 1, N'ChannelReading_926371_12_2A775060-D371-4929-BA17-E119CF7CD2A8')
INSERT [#zzitems] ( [DeviceID], [ChannelID], [SerialNumber], [DeviceChannelID], [ReadingDownloadName], [ReadingStartDate], [ReadingEndDate], [Amount], [Sortorder], [IncludeFirstDate], [DownloadTableName]) VALUES ( N'264ab60e-104f-42da-888b-b93a368c9ce4', N'15', N'926371', N'9de4017b-6bb4-4d1f-a09e-e7fafaaca8a8', N'ChannelReading_Dummy4A03C53F-B63B-4721-A921-DE0C2316065F', CAST(N'2022-05-20T13:49:08.0000000' AS DateTime2), CAST(N'2023-01-23T11:05:33.0000000' AS DateTime2), 50, N'Ascending', 1, N'ChannelReading_926371_15_9DE4017B-6BB4-4D1F-A09E-E7FAFAACA8A8')
GO
 
delete from [#zzitems] where id in (1,3,5)

drop table if exists #temp_zzitems 
select * into #temp_zzitems from [#zzitems] 
truncate table #zzitems
INSERT INTO [#zzitems]
(
[DeviceID], [ChannelID], [SerialNumber], [DeviceChannelID], [ReadingDownloadName], [ReadingStartDate], [ReadingEndDate], [Amount], [Sortorder], [IncludeFirstDate], [DownloadTableName]
)
SELECT [DeviceID], [ChannelID], [SerialNumber], [DeviceChannelID], [ReadingDownloadName], [ReadingStartDate], [ReadingEndDate], [Amount], [Sortorder], [IncludeFirstDate], [DownloadTableName]
  FROM #temp_zzitems
 ORDER BY ID
drop table if exists #temp_zzitems 

SELECT * FROM #zzitems

identity

kaj SSCommitted Points: 1605 More actions · Answer 9

This is not a reseed per se, but since a TRUNCATE TABLE will restart the identity seed, you can use a temp table to store a copy of the data, do a truncate table, and re-insert the data again, i.e.

select * 
into #t_zzitems
FROM [#zzitems];

truncate table [#zzitems]

insert into [#zzitems] 
select  [DeviceID], [ChannelID], [SerialNumber], [DeviceChannelID], [ReadingDownloadName], [ReadingStartDate], [ReadingEndDate], [Amount], [Sortorder], [IncludeFirstDate], [DownloadTableName]
from #t_zzitems
order by Id;

drop table #t_zzitems;

EDIT: I now see that this is almost exactly the same as what Jonathan AC Roberts wrote.

Guess you have to do a page refresh once in a while in order to keep up with the other entries. 🙂 Anyway, the duplication was not intentional.

This reply was modified 1 year, 10 months ago by kaj.
This reply was modified 1 year, 10 months ago by kaj.

Ed B Hall of Fame Points: 3666 More actions · Answer 10

Weegee71 wrote:

I got the following message
Msg 4108, Level 15, State 1, Line 284 Windowed functions can only appear in the SELECT or ORDER BY clauses
How can i re-set the ID in this temp table after I have removed records?

I agree that row by row processing is rarely a good idea and even then you can increment a counter variable without the need for a column to be sequential, but if you need to update a column using a windowed function you need to nest it in a derived table.

ALTER TABLE [#zzitems] ADD id2 int

Update i
set i.id2 = i.rownum
from (
        select *,
        row_number() OVER ( order by DeviceID, DeviceChannelID, ReadingStartDate ) as rownum
        from [#zzitems] 
      ) as i

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 11

To be honest, I'll never understand the idea of creating a Temp Table and then deleting rows from it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

How to Reseed an Identity Column on Temp Table After data has been removed

Attachments: