Search code examples
sqlsql-serversql-server-2008t-sqlidentity-insert

How to choose to use IDENTITY_INSERT or provide one in a single statement

I am working on a Stored Procedure that needs to re-insert elements in a table with an identity column. Doing this, for some rows it needs to specify the identity column, for some others it would be preferable for a new identity value the be generated by the identity column. Is it possible of doing this in a single statement instead of an IF-ELSE ?

Here the solution I have for now (where id_column is the identity column) :

DECLARE @id_to_insert INT, @val1 INT, @val2 INT, @val3 INT, @val4 INT;

-- Do some things

IF @id_to_insert IS NOT NULL
BEGIN
    SET IDENTITY_INSERT dbo.table1 ON;

    INSERT INTO dbo.table1 (id_column, col1, col2, col3, col4, col4)
    VALUES (@id_to_insert, @val1, @val2, @val3, @val4);

    SET IDENTITY_INSERT dbo.table1 OFF;
END
ELSE
BEGIN
    INSERT INTO dbo.table1 (col1, col2, col3, col4, col4)
    VALUES (@val1, @val2, @val3, @val4);
END

As you can see, both statements are very similar, it would be interesting not to have to repeat almost the same insert.

EDIT:

This table holds current contacts data. I move archived/deleted contacts to an other table so we keep this table clean and free of unwanted data, but I may sometimes restore them to the current data table. If possible I would like to keep the identity the contact had before being archived/deleted. If not possible (the identity was already reassigned) I need to generate a new one.

Solution

  • I strongly recommend not using identity inserts in general day to day usage. Identity insert requires users to own the table or have alter permission on the table, which is not a good idea to give to general users. The identity insert functionality exists to allow the migration of data and should generally only be used in a maintenance type of mode, not something that should be happening as a regular occurrence.

    Could you instead add an ActiveFlag field to your table? You can then create a clustered index on (ActiveFlag, id_column). This would give you the benefit of segregating your data for fast access to the new and old data. You also wouldn’t have all the overhead of moving data back and forth between the tables as they become expired or are rejuvenated; it’s just a field update to the flag. Keeping two separate tables seems like it adds more work for little to no benefit.