error message with try part of try catch when cursor updating tables

Question

error message with try part of try catch when cursor updating tables

Viewing 4 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

It is telling you exactly what is wrong in the error message. There is an identity column in the tables and you are trying to insert an identity from one table into another. You need to explicitly state the column name in the query and turn identity insert on, do the insert, then turn identity insert off if the identity value needs to move from the source table to the target table.

This means you need to dynamically build the column list for the tables. If the identify value doesn't move from source to target, you still need to dynamically build the column list but not include the identity column. This would also mean you wouldn't need to turn identity insert on then off.

This is why I mentioned that you didn't give us enough information in your other post to really help you. I provided a snippet based on incomplete information.

Johnson Welch SSCoach Points: 18773 More actions · Answer 2

SQL Error 8101 occurs when user has attempted to insert a row containing a specific identity value into a table that contains an identity column. See this, this will help you out:
https://blog.sqlauthority.com/2007/03/28/sql-server-fix-error-8101-an-explicit-value-for-the-identity-column-in-table-can-only-be-specified-when-a-column-list-is-used-and-identity_insert-is-on/

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Would be nice if you would provide all the information needed to answer your request for for help. For instance, nothing you have posted even shows how this table, #TempCommonMatchFormatted, is even created. Really getting tired of having to guess what you need. But, since that is all we have I hope this at least points you the right direction:

select [tab].[name], stuff((select N', ' +[col].[name] from [sys].[columns] as [col] where [tab].[object_id] = [col].[object_id] and [col].[is_identity] = 0 order by [col].[name] for xml path(''),TYPE).value(N'./text()[1]',N'nvarchar(max)'),1,2,N'') from [sys].[tables] as [tab];

ScottPletcher SSC Guru Points: 101207 More actions · Answer 4

Isn't this the third iteration of the same essential q? Why do you keep re-posting the same q??

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".