October 2, 2018 at 3:15 pm
I have the code below which inserts records found only in tables in one database, into the matching tables in another database. I have a cursor that pulls the database schema and table names ("db1" or "db2") from a temp table called #TempCommonMatchFormatted and insert the records by replacing the table names in a sql template, and executing it. I added a try catch statement to the sql template because every once in a while there's an issue with a difference in one of the tables, between the old database and the new one, which prevents the records from being inserted. So when the cursor encountered that situation I just wanted it to display to the screen "insert_error" and the table name, then go on to the next table. I've run it with a rollback and it seems to be working correctly, except it finishes and there's a message at the bottom left with an exclamation point that says "Query finished with errors." When I go back through the messages that printed while the cursor was running every once in a while when there was an insert issue there's a message like:
"Msg 8101, Level 16, State 1, Line 25
An explicit value for the identity column in table 'sysn.dbo.ULog' can only be specified when a column list is used and IDENTITY_INSERT is ON.
"
But the code doesn't crash, it displays "insert_error" with the table name and moves on to the next table. My question is if I change the final "rollback tran" in the code to "commit tran" will the cursor update all the tables that don't throw an error and roll to the next table as intended? I'm wondering if the "query completed with errors" message is just letting me know that there was an error but the catch part of the try catch statement handled it.
Code:begin tran
--Declare Parameters
Declare @DB1 Varchar(MAX)
Declare @DB2 Varchar(MAX)
--testing code
SET @DB1='sfd'
SET @DB2='sysn'
--template for cursor
declare @SQLCmdTemplate nvarchar(MAX) = N'
begin try
select
''~db1~'' as UpdateTable
, count(*) as UpdateCnt
from
(
select * from ~db1~
except
select * from ~db2~
) as dt
end try
begin catch
select ''~db1~'' as Compare_error
end catch
begin try
insert into ~db2~
select * from (
select * from ~db1~
except
select * from ~db2~
)a
end try
begin catch
select ''~db2~'' as insert_error
end catch
';
--add cursor
declare @SQLCmd nvarchar(MAX)
declare @field1 varchar(MAX)
declare @field2 varchar(MAX)
declare cur CURSOR LOCAL for
select db1, db2 from #TempCommonMatchFormatted
open cur
fetch next from cur into @field1, @field2
while @@FETCH_STATUS = 0 BEGIN
set @SQLCmd = replace(replace(replace(replace(@SQLCmdTemplate,'~db1~',quotename(@field1)),'~db2~',quotename(@field2)),'[',''),']','');
print @SQLCmd;
exec sys.sp_executesql @stmt = @SQLCmd;
fetch next from cur into @field1, @field2
END
close cur
deallocate cur
rollback tran
October 2, 2018 at 6:03 pm
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.
October 2, 2018 at 11:00 pm
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/
October 3, 2018 at 3:36 pm
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];
October 3, 2018 at 3:49 pm
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) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply