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 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy