0

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?

Nightmare Games
  • 2,205
  • 6
  • 28
  • 46
  • You can't use SET IDENTITY_INSERT on *table variables*. Why do you need @toInsert in the first place? – Giorgos Betsos Dec 10 '14 at 19:50
  • @GiorgosBetsos: That's a good question, and if I understood sql better I might be able to answer that. As it is, this procedure is based on an example my boss sent me. – Nightmare Games Dec 10 '14 at 20:00
  • 1
    Taking the rows from one table to insert them into another table like that is pointless. Just make your insert from the parameter. There is no need to create a copy of it. – Sean Lange Dec 10 '14 at 20:01
  • @SeanLange: You're right, that doesn't seem to make any sense. – Nightmare Games Dec 10 '14 at 20:19

2 Answers2

1

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
Sean Lange
  • 33,028
  • 3
  • 25
  • 40
  • Apparently the person who wrote the function recalls getting an error the last time they tried to copy directly, but your change seems to work fine with mine now, so I'm going to go with that. Thanks. – Nightmare Games Dec 10 '14 at 20:21
0

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.

Nightmare Games
  • 2,205
  • 6
  • 28
  • 46