CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Zinov
Zinov

Reputation: 4119

Set Identity ON with a merge statement

I am inserting and deleting elements in a table, as a result, when I want to insert a new element, it takes a new id number, but this id is not taking the last id+1. For example: the last id is 5 and I inserted a 5 elements and deleted after that, the new id will take the value of 11, and I need 6. Here is my code 

CREATE TABLE #FC
(
    Code varchar(25),
    Description varchar(50),
    Category varchar(10),
    CreatedDate datetime,
    LastModifiedDate datetime
);

--Adding just one record
INSERT INTO #FC (Code, Description, Category, CreatedDate, LastModifiedDate)
VALUES          ('DELETE_MEMBER', 'Delete Member', 'POLICY', @Now, @Now);
;


SET IDENTITY_INSERT [dbo].[Function_Code] ON;

MERGE
    INTO [dbo].[Function_Code] AS T
    USING #FC AS S
    ON (T.Code = S.Code) AND (T.Description = S.Description) AND(T.Category = S.Category)
    WHEN MATCHED THEN
        UPDATE SET
            [Code] = S.[Code]
            , [Description] = S.Description
            , [Category] = S.Category
            , [CreatedDate] = S.CreatedDate
            , [LastModifiedDate] = S.LastModifiedDate
    WHEN NOT MATCHED THEN
        INSERT (Code, Description, Category, CreatedDate, LastModifiedDate)
        VALUES(S.Code, S.Description, S.Category, S.CreatedDate, S.LastModifiedDate)
;

SET IDENTITY_INSERT [dbo].[Function_Code] OFF;

Upvotes: 2

Views: 1544

Answers (2)

jazzytomato
jazzytomato

Reputation: 7214

An identity is a technical field that you should not handle yourself. If you want to manage the sequence yourself, then don't use an identity field.

Nevertheless, if you really want to do it, you'll have to reseed the table to the desired value :

DELETE YourTable

DECLARE @n INT;
SELECT @n = MAX(YourId) FROM YourTable
DBCC CHECKIDENT ('YourTable', RESEED, @n)

INSERT YourTable

Upvotes: 2

Vulcronos
Vulcronos

Reputation: 3456

What you are asking is dangerous. If you make a column an identity column, don't touch it, let sql server do its job. Otherwise you can start getting primary key errors. The identity column is ready to insert 11. You insert six through eleven in your code by running it multiple time and you can get a primary key error next time the identity tries to insert a row into the table.

As Thomas Haratyk said you can reseed your table. Or you can use:

select MAX(YourId) + 1 FROM YourTable

and insert that into your identity column if you are sure you will always insert an id that has already been used by the identity column.

However, if you are commonly overwriting the default identity behavior, it may be better to manage this column yourself because deleting from an identity column results in gaps by default.

Upvotes: 0

Related Questions