October 3, 2018 at 10:19 am
When I run the script below I get the error message below because it's trying to insert records in to an identity column that has identity seed. I use this same script over and over again in a cursor updating many tables using a template and replacing the table. So I'm wondering if there is a way to modify the script below so that it will automatically insert records from all columns except the identity column, (if one exists in the table.) Any tips are greatly appreciated.
Code:
begin try
insert into db2.dbo.table1
select * from (
select * from db1.dbo.table1
except
select * from db2.dbo.table1
)a
end try
begin catch
select 'db2.dbo.table1' as insert_error;
insert into #TempInsertError (InsertError) Values('db2.dbo.table1')
end catch
error:
Msg 8101, Level 16, State 1, Line 25
An explicit value for the identity column in table 'db2.dbo.table1' can only be specified when a column list is used and IDENTITY_INSERT is ON.
October 3, 2018 at 10:40 am
scotsditch - Wednesday, October 3, 2018 10:19 AMWhen I run the script below I get the error message below because it's trying to insert records in to an identity column that has identity seed. I use this same script over and over again in a cursor updating many tables using a template and replacing the table. So I'm wondering if there is a way to modify the script below so that it will automatically insert records from all columns except the identity column, (if one exists in the table.) Any tips are greatly appreciated.
Not that I know of. If you wish to insert only a subset of columns rather than all, you need to explicitly name the columns being inserted (in both source and target).
You can probably craft this using dynamic SQL, but it will require some effort to do so.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 3, 2018 at 10:44 am
I think you could use an INSTEAD OF INSERT trigger to do that, adding a view if necessary to avoid SQL "compile"/pre-scan issues. Of course the trigger would apply to all INSERTs that occur while the trigger is in place, but I don't see why that would normally be any kind of issue, and you could even use SESSION_CONTEXT to customize the effect of the trigger if you needed to.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
October 3, 2018 at 11:56 am
Use dynamic SQL and build your query joining sys.tables and sys.columns on object_id where is_identity = 0 and a cursor.
October 3, 2018 at 1:51 pm
Why are you posting new threads when you have already asked this question and been given an answer in another post. You aren't helping yourself doing this.
Here is the post from yesterday: https://www.sqlservercentral.com/Forums/1998288/error-message-with-try-part-of-try-catch-when-cursor-updating-tables#bm1998317
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply