INSERT into an identity column not allowed on table variables

Question

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.

Answer 1

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.