INSERT into an identity column not allowed on table variables
c#, sql-server-2008
Solution
The error is fairly clear: you are not allowed to do what you are trying to do. Basically, you are going to have to find a design that is not dependent on inserting the identity value into the table-variable / table-valued-parameter. My advice would be to create a separate table-variable (unrelated to the table-valued-parameter) which has the same data, but which does not have the `IDENTITY` column, so...
declare @foo table (id int not null, name nvarchar(200) not null /* etc */)
insert @foo (id, name /* etc */)
select id, name /* etc */ from @statetable
at which point `@foo` has the original data, but does not have an identity column - you can then do whatever you want with `@foo`.
Without seeing what you are doing with your identity insert, it is hard to comment much further.
Problem
I am importing data from large excel sheet and storing it in a stateTable. Now I have to push this data into a database table. The table does have an identity column(1,1). I have created a similar table type in DB and a procedure to take a parameter as table type to insert in the particular table. I have also set ON the identity insert. My code is: ``` using (SqlCommand command = new SqlCommand("InsertStateTable") { CommandType = CommandType.StoredProcedure}) { SqlParameter param = command.Parameters.AddWithValue("@statetable", dt); param.TypeName = "StateTable"; param.SqlDbType = SqlDbType.Structured; command.Connection = con; con.Open(); command.ExecuteNonQuery(); con.Close(); } ``` But the error that arises is "INSERT into an identity column not allowed on table variables." I have gone thru many sites but no specific reason is given..... Thanks in advance.