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

  • 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.
  • 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 5 days, 19 hours ago by  Thom A.

    Thom~

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

  • 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

  • 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

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

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • 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) A socialist is someone who give you the shirt off *someone else's* back.

  • 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/

  • 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.

    Dear Lord... I'm a production DBA. Please grant me patience because, if you grant me strength, I'm gonna need bail money to go with it.


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

  • 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

     

  • 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 5 days, 12 hours ago by  kaj.
    • This reply was modified 5 days, 12 hours ago by  kaj.
  • 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
  • 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.

    Dear Lord... I'm a production DBA. Please grant me patience because, if you grant me strength, I'm gonna need bail money to go with it.


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

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

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