Stored Procedure IDENTITY_INSERT
I'm recently change my data table, I remove column and add a new column that define as identity = True and identity seed = 1, identity increment = 1. When i tried to insert data to this table by STORE PROCEDURE i get this exception:
An explicit value for the identity column in table 'AirConditioner' can only be specified when a c开发者_运维知识库olumn list is used and IDENTITY_INSERT is ON.
I saw that i need to add this lines: SET IDENTITY_INSERT [dbo].[AirConditioner] ON and finally OFF I added and its still throw an exception...
My store procedure is attached as a picture
SQL Server will take care of assigning a value to your identity column. You should remove @ID
from the insert statement and remove the IDENTITY_INSERT
statements.
can only be specified when a column list is used and IDENTITY_INSERT is ON.
You're forgetting the first condition: the column list. The column list is a (usually) optional element between the table name and values
. You should specify it like:
INSERT INTO AirConditioner (ID, CategoricNumber, ...)
VALUES(@ID, @CategoricNumber, ...)
Do NOT use set identity insert on. If you have an identity, you should be letting SQL server decide what value to put in there.
ALTER PROCEDURE [dbo].[AddAirConditioner] @CategoricNumber int, @Name nvarchar(50),
@PicName nvarchar(100), @ShortDetails nvarchar(200), @Details nvarchar(2000),
@Price int, @ImagePath nvarchar(500), @AirConditionerType nvarchar(50), @COP float,
@BTU float, @ProdType nvarchar(20), @ProdIndex int
AS
INSERT INTO AirConditioner VALUES( @CategoricNumber, @Name, @PicName,
@ShortDetails, @Details, @Price, @ImagePath, @AirConditionerType, @COP,
@BTU, @ProdType, @ProdIndex)
If you need to get the ID back for using in child tables, then use scope_identity or the output clause. Look these up in Books online to see how to use.
Try placing
SET IDENTITY_INSERT [dbo].[AirConditioner] ON
GO
Before the alter procedure
精彩评论