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?