2

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.

MaxiWheat
  • 6,133
  • 6
  • 47
  • 76
  • Sure, I have no issue with making a condition to turn `IDENTITY_INSERT` ON or OFF, that was the idea I had, but can't get the INSERT part to work for both situations in a single query. – MaxiWheat May 19 '15 at 18:14
  • 1
    What you have is fine as written to do what you are asking to do. The real question is how can you redesign your database so that you can avoid identity inserts in the first place. 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. Add context to your question and maybe we can help you eliminate the need. – Brian Pressler May 19 '15 at 18:43
  • @BrianPressler I edited my question with more context information – MaxiWheat May 20 '15 at 14:49

2 Answers2

2

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.

Brian Pressler
  • 6,653
  • 2
  • 19
  • 40
0

About the closest thing you can do is something like this psuedocode:

IF @id_to_insert IS NULL
  SET @id_to_insert = SELECT [the next identity value] FROM MyTable;

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;

And you'd probably need to use a transaction to handle concurrency issues.

Tab Alleman
  • 31,483
  • 7
  • 36
  • 52
  • I'm not sure what you mean. Removing the identity property? I'm assuming there are other input points to the table that require it. – Tab Alleman May 19 '15 at 18:49