March 17, 2005 at 10:44 am
Hi all,
I like to insert a row from a table back into the same table. In another word I want to duplicate the same record in the table in the stored procedure but it is generating the error below. There is identity column in the table but I'm why I can't insert a row and how I can fix this. Thanks in advance for any help.
MS SQL-DMO(ODBC SQLState: 23000)
Error 8101: An explicit value for identity column in table’tblFinal_inspection’ can only be specified when a column list is used and IDENTITY_INSERT is ON.
this is my sp:
--start a fi and undo addon of a sample
CREATE PROCEDURE dbo.spFI_Add
@sampler_id int
AS
declare @lotnum int
SELECT @lotnum = lotnum FROM tblSampling_data where sampler_id = @sampler_id AND disposition is null;
if @@rowcount = 1
Begin
INSERT INTO tblFinal_inspection
SELECT * FROM tblFinal_inspection Where lotnum = @lotnum;
return(1);
End
Else
return(0);
GO
March 17, 2005 at 11:03 am
Supply a column list, for all columns excluding the identity column. Supply the matching column list in the Select. This is a best practice anyway, and is something you should always be doing.
INSERT INTO tblFinal_inspection (Column1, Column2 ... ColumnN)
SELECT Column1, Column2 ... ColumnN FROM tblFinal_inspection Where lotnum = @lotnum;
In the above, replace "Column1, Column2 ... ColumnN" with every column in the table except the identity column
March 17, 2005 at 11:14 am
Hi PW,
Thanks for your reply.
I have some 20 columns in the tblFinal_inspection. Is that the only way to insert a duplicate record w/o inserting the identity column?
Thanks!
March 17, 2005 at 11:42 am
I think that is the only way. Besides, it really is the correct way to do it and is a good habit to have. Since you're inserting back into itself, this problem won't happen, but you can have a problem when inserting into other tables where the column order may have changed.
Anyway, there is something you can do to help: use EM's query builder to build your script. Go to tblFinal_Inspection, right-click, select Open Table, then select Query. On the toolbar there's an icon for Change Query Type. Select Insert From. In the dialog box, select tblFinal_Inspection (the table you're inserting into). Next, remove the * line from the grid. Now you can simply select which columns to use by checking the box next to it in the tblFinal_Inspection table in the top section. Select all but the identity. You will see the code in the SQL section. Just copy and paste that when you're done. This is a painless way of making your insert statement.
HTH!
March 17, 2005 at 12:16 pm
Thanks Brian!
That helps alot.
March 18, 2005 at 12:23 pm
You can also create a view one the table that excludes the indentity column, then insert from the view back into the table (using select *).
Of course, as PW pointed out, this is only acceptable if there is no chance of the table changing. Otherwise use a column list.
For a quick and easy column list, simply drag the "Columns" level from your Query Analyser Object browser to the query screen...this automatically produces a csv list of all column names in that table.
cl
Signature is NULL
March 18, 2005 at 12:54 pm
Another great idea.
Thanks Calvin!
March 18, 2005 at 3:49 pm
Hi,
another possibility is to follow the suggestion of the error message.
Use the "SET IDENTITY_INSERT [tablename] ON" command to enable inserting a row with the identity column specified. This is especially useful if you want to transfer some rows from a table to a copy with the same numbers in the identity column.
When you're done, use "SET IDENTITY_INSERT [tablename] OFF" to return to the normal behaviour.
Regards
Christian
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply