I have a table with an identity column that automatically increments the value by one when a new row is inserted. Every once in a while, I need to be able to manually enter the value for that identity column. I know this isn't typical practice; however, this database is a part of a new system that occasionally requires old data to be added to it where that old data must maintain its original Id. The program supplying the data confirms prior to the insert that the manually supplied id is not a duplicate. Besides that, the identity column is the table's primary key, so the database shouldn't accept a duplicate value.
It seems that, in order to manually enter a value, I will need to use SET IDENTITY_INSERT [TableName] ON prior to the insert and SET IDENTITY_INSERT [TableName] OFF following the insert.
Beyond that, I'm wondering if it is possible to write one INSERT statement that handles both an automatic or manual insert. Ideally, if it saw a NULL value for the id value, it'd automatically popular the id value; however, if there were an id value supplied, it would use that. Here is what I have so far:
-- Create original table with existing sample data
CREATE TABLE #o
(
[Id] int identity(10, 1),
[First Name] varchar(50),
[Last Name] varchar(50)
)
INSERT INTO #o ([First Name], [Last Name])
SELECT *
FROM (
VALUES ('Dwight', 'Schrute'), ('Jim', 'Halpert')) x(a, b)
SELECT *
FROM #o
-- Create a table to supply a row of data where the Id could be
-- NULL (requiring the use of the automatically supplied identity
-- value) or it could be a unique value to insert into the table
DECLARE @id int = 4
SELECT *
INTO #t
FROM (
VALUES(@id, 'Michael', 'Scott')
) t([Id], [First Name], [Last Name])
SELECT *
FROM #t
-- If the value of the id is not NULL, change IDENTITY_INSERT to ON
IF (SELECT [Id] FROM #t) IS NOT NULL
SET IDENTITY_INSERT #o ON
-- Insert the new data
INSERT INTO #o ([Id], [First Name], [Last Name])
SELECT
#t.[Id], #t.[First Name], #t.[Last Name]
FROM #t
LEFT JOIN #o ON #o.[Id] = #t.[Id]
WHERE #o.[Id] IS NULL
-- Turn off the IDENTITY_INSERT
SET IDENTITY_INSERT #o OFF
SELECT *
FROM #o
ORDER BY [Id]
DROP TABLE #t
DROP TABLE #o
This works if a value is supplied for the Id; however, if NULL is used for the @Id variable to indicate that SQL should provide the Id, then I get the following error:
Msg 544, Level 16, State 1, Line 37
Cannot insert explicit value for identity column in table '#o' when IDENTITY_INSERT is set to OFF.
I know I could rewrite the INSERT statement to look like this for when the Id is NULL:
-- Insert the new data
INSERT INTO #o ([First Name], [Last Name])
SELECT #t.[First Name], #t.[Last Name]
FROM #t
LEFT JOIN #o ON #o.[Id] = #t.[Id]
WHERE #o.[Id] IS NULL
But of course, this wouldn't work for when the Id is not NULL and, again, I'm wondering if I can do it with one INSERT statement.
NOTE: I am using a LEFT JOIN and WHERE statement because this is a part of a larger stored procedure to UPDATE when the provided Id exists in the data table and INSERT when it does not.
Honestly, I think altering the INSERT appropriately for if you want to use the IDENTITY or not is the "best™" solution. If you just want the IDENTITY omit the column from the INSERT INTO... clause and don't provide a value in the SELECT/VALUES clause. If you do want to provide a value, enable IDENTITY_INSERT and then do include the column in the INSERT INTO... clause and SELECT/VALUE clause.
You could, however, use a SEQUENCE which could enable to you to not change the INSERT INTO... clause and, assuming you are using a parametrised statement, use the next value from the SEQUENCE if the value is NULL.
If, however, you need multiple rows, and you're using a TVP, then you could pass NULL values for the ID and then UPDATE the NULL values in the logic before INSERTing the values. If this data is coming from a different table, however, you'd need to use an intermediary table (such as a temporary table) and UPDATE that. You can't use NEXT VALUE FOR for some rows and not for others; FOR NEXT VALUE isn't allowed in a CASE, ISNULL, and several other functions that would permit the logic, nor can it be used in a UNION (ALL) query.
CREATE SEQUENCE dbo.YourTableIDSequence
AS int
START WITH 10
INCREMENT BY 1;
GO
CREATE TABLE dbo.YourTable ([Id] int NOT NULL
CONSTRAINT DF_YourTableID
DEFAULT NEXT VALUE FOR dbo.YourTableIDSequence
CONSTRAINT PK_YourTable PRIMARY KEY,
[First Name] varchar(50),
[Last Name] varchar(50));
GO
--Parameters from outside
DECLARE @ID int = NULL,
@Firstname varchar(50) = 'Leah',
@LastName varchar(50) = 'Unknown';
--Your actual batch starts here:
IF @ID IS NULL
SET @ID = NEXT VALUE FOR dbo.YourTableIDSequence;
INSERT INTO dbo.YourTable (Id,
[First Name],
[Last Name])
VALUES(@ID, @Firstname, @LastName);
GO
--Parameters from outside
DECLARE @ID int = 12345,
@Firstname varchar(50) = 'Thom',
@LastName varchar(50) = 'A';
--Your actual batch starts here:
IF @ID IS NULL
SET @ID = NEXT VALUE FOR dbo.YourTableIDSequence;
INSERT INTO dbo.YourTable (Id,
[First Name],
[Last Name])
VALUES(@ID, @Firstname, @LastName);
GO
CREATE TYPE dbo.YourTableType AS TABLE (ID int NULL,
FirstName varchar(50) NOT NULL,
LastName varchar(50) NOT NULL);
GO
--Parameters from outside
DECLARE @YT YourTableType;
INSERT INTO @YT (Id,
FirstName,
LastName)
VALUES(NULL,'Joe','Blogss'),
(987654,'Jane','Smith');
--Your batch starts here
UPDATE @YT
SET ID = NEXT VALUE FOR dbo.YourTableIDSequence
WHERE ID IS NULL;
INSERT INTO dbo.YourTable (Id,
[First Name],
[Last Name])
SELECT ID,
FirstName,
LastName
FROM @YT;
GO
SELECT *
FROM dbo.YourTable;
GO
--Clean up
DROP TABLE dbo.YourTable;
DROP SEQUENCE dbo.YourTableIDSequence;
DROP TYPE dbo.YourTableType;