September 2, 2016 at 11:15 am
Hi all,
(using SQL 2008 Standard, , database compatibility set to SQL 2005).
I am getting this error for the MERGE statement below:
[font="Courier New"]Msg 544, Level 16, State 1, Line 43
Cannot insert explicit value for identity column in table 'LocDisplay' when IDENTITY_INSERT is set to OFF.[/font]
However I am confused as to why the target table seem to be cause the problem according the error message.
The target table does have an identity column ([font="Courier New"]LocId[/font]) and I do intend for it to generate the keys.
The output table is a copy of the target table and hence also has the identity key, BUT i did set identity insert for the target table to ON.
I also did not specify an explicit value for the [font="Courier New"]LogId [/font]in the insert statement, so what gives?
I appreciate any help.
Thanks.
select top 0 action=Cast(null as nvarchar(20)), * into LocDisplay_merge_output_20160902 from LocDisplay;
set identity_insert LocDisplay_merge_output_20160902 on;
merge into LocDisplay as T
using (
select LocCode, PlaneCode, LocName, SomeCode
from [Loc] as S
cross apply (select distinct SomeCode from LocDisplay as SD where
SD.PlaneCode = S.PlaneCode and
SD.LocCode = S.LocCode) as SD) as S on
S.SomeCode = T.SomeCode and
S.LocCode = T.LocCode and
S.PlaneCode = T.PlaneCode
when matched and NullIf(S.LocName, T.LocName) is not null then
update set LocName = S.LocName
when not matched then
insert(LocCode,LocName,PlaneCode,SomeCode)
values(S.LocCode,S.LocName,S.PlaneCode,S.SomeCode)
output $action
,IsNull(deleted.LocId, inserted.LocId)
,IsNull(deleted.LocCode, inserted.LocCode)
,IsNull(deleted.PlaneCode, inserted.PlaneCode)
,IsNull(deleted.SomeCode, inserted.SomeCode)
into LocDisplay_merge_output_20160902(action, LocId, LocCode, PlaneCode, SomeCode);
September 2, 2016 at 11:49 am
I even tried this one but I get the same error.
select distinct SomeCode, PlaneCode into #t from LocDisplay
set identity_insert LocDisplay_merge_output_20160902 on;
insert into LocDisplay(LocCode, LocName, SomeCode, PlaneCode)
output 'INSERT', inserted.LocId, inserted.LocCode, inserted.LocName, inserted.SomeCode, inserted.PlaneCode
into LocDisplay_merge_output_20160902(action, LocId, LocCode, LocName, SomeCode, PlaneCode)
select LocCode, LocName, SomeCode, PlaneCode from (
select LocCode, SomeCode, LocName, PlaneCode
from [Loc] as S
cross apply (select PlaneCode from #T as SD where
SD.SomeCode = S.SomeCode) as SD) as S where not Exists(select top 1 null from LocDisplay as T where
S.PlaneCode = T.PlaneCode and
S.LocCode = T.LocCode and
S.SomeCode = T.SomeCode)
September 2, 2016 at 12:08 pm
So far, the solution for my particular problem is to simply convert the identity column in the OUTPUT table to a non-identity one.
It's a pain if I have to do it for multiple tables for which some of them have many columns, but it seems to work.
I would still like to know if someone could point out what is wrong with my original MERGE statement as it seems to look 'correct'.
Thank you in advance..
September 2, 2016 at 2:12 pm
The error you posted doesn't complain about writing to your output table of LocDisplay_merge_output_20160902, rather inserting into your main table LocDisplay.
Can you confirm the error message is correct?
September 2, 2016 at 3:57 pm
Thanks for the reply, Nick.
That IS the message I am getting with the above statement, and that's just the thing, it's complaining about the target table (INSERT) LocDisplay and not the output table LocDisplay_merge_output_20160902, it confuses me.
So why is it complaining about the LocDisplay when
1. identity_insert in set for LocDisplay_merge_output_20160902 on (and there can only be one table in a session that can have identity_insert on at any time), so identity_insert for LocDisplay SHOULD BE implicitly OFF.
2. I am not explicitly inserting to the identity column LocID in LocDisplay.
September 4, 2016 at 6:09 am
You are merging into the LocDisplay table and that's where the error originates
😎
merge into LocDisplay as T
using (
select LocCode, PlaneCode, LocName, SomeCode
from [Loc] as S
cross apply (select distinct SomeCode from LocDisplay as SD where
SD.PlaneCode = S.PlaneCode and
SD.LocCode = S.LocCode) as SD) as S on
S.SomeCode = T.SomeCode and
S.LocCode = T.LocCode and
S.PlaneCode = T.PlaneCode
when matched and NullIf(S.LocName, T.LocName) is not null then
update set LocName = S.LocName
when not matched then
insert(LocCode,LocName,PlaneCode,SomeCode)
values(S.LocCode,S.LocName,S.PlaneCode,S.SomeCode)
September 6, 2016 at 10:20 am
OK, so i went back to try setting identity_insert on LocDisplay instead of the output table. I was worried that the merge would fail in the INSERT section... but it did not!?
Instead the MERGE worked fine?!?. By setting identity_insert LocDisplay on (instead of the output table LocDisplay_merge_output_20160902):
1. The WHEN MATCHED-UPDATE section worked (never had a problem there)
2. The WHEN NOT MATCHED-INSERT section worked, even though I did not explicitly specify values for the identity column LocId, and with IDENTITY_INSERT on to boot
3. The OUTPUT section worked, with explicit values specified for the IDENTITY column LocId even though IDENTITY_INSERT LocDisplay_merge_output_20160902 wasn't set (to ON)
So I guess the error message is correct, just that #2 and #3 does not immediately make sense to me but it worked.
Can anyone comment on that? It's sort of not intuitive but there must be some logic to it.
September 6, 2016 at 11:39 am
Your output table won't have an identity column, so you shouldn't need to set it for that.
September 6, 2016 at 1:55 pm
actually it does. If you "select * into <copy> from <source>", which is basically what I did to create the output table, you'll find that the same columns that are IDENTITYs in <source> will be IDENTITYs in <copy>.
This whole mess is just odd behavior on SQL's part i guess 🙂
try this:
create table t1 (id int identity(1,1), meh int);
select * into t2 from t1;
then check the data types ins t2.
September 6, 2016 at 2:42 pm
Frankly,IMHO, that should be a bug. That shouldn't be copied over.
September 7, 2016 at 10:41 am
edited. double post.
September 7, 2016 at 10:43 am
I'd would add though that the original issue I encountered should be a bug.
Thanks to all those who responded to this topic. 😎
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply