Sql Script Error in Stored Procedure for Insert Entry

Question

I'm working on a stored procedure to bulk add entries to a table.

Here's what I've got thus far:

use [DebugDatabase]
go

set ansi_nulls on
go

set quoted_identifier on
go

create procedure [dbo].[AddMultipleErrors]
    @errors ErrorTableType readonly

as

declare @toInsert ErrorTableType

begin
insert into @toInsert select * from @errors
insert into [dbo].[Error] (ErrorMessage, FileId) select ErrorMessage, FileId from @toInsert
end
go

I'm getting the message "An explicit value for the identity column in table '@toInsert' can only be specified when a column list is used and IDENTITY_INSERT is ON."

I've tried adding the line "set identity_insert @toInsert on" after the "declare" statement, but I'm getting an "incorrect syntax" error. I've tried moving it to a few other locations with the same result.

Now, I've done some research, and it looks like I don't actually want to set "identity_insert" to on anyway, because it will then require me to specify an id rather than have the table generate one automatically, which is what I do want.

Supposedly, all I need to do is specify each separate column in the table in the insert call, leaving out the id value, and the everything should match up fine. But, unless I'm mistaken, the procedure is ALREADY set up that way and it's still not liking it.

Is there something I could be missing?

Answer 1

Alright, this is what we figured out:

The reason I'm getting the error about the ID isn't because of the insert procedure, but because of the table type. In ErrorTableType I included the Id along with the other columns. Removing Id from the table type (but keeping it in the table itself) seems to fix the error.

Answer 2

Your entire procedure can be greatly simplified to this.

create procedure [dbo].[AddMultipleErrors]
    @errors ErrorTableType readonly
as

insert into [dbo].[Error] (ErrorMessage, FileId) 
select ErrorMessage, FileId from @errors

go