October 1, 2019 at 2:26 pm
Yeah i know Heb. Let me post my complete query. Its like this (pls check if there's something wrong):
Insert into scdmain(
Id, col1, col2, col3, scdstartdate, scdenddate
)
Select Id, col1, col2, col3, scdstartdate, scdenddate
from
(
Merge into scdmain as target using
(Select id, col1, col2, col3, binary_checksum(id, col1, col2, col3) as code from scdstg
) as source
(id, col1, col2, col3, code)
On (target.id = source.id)
When matched and binary_checksum(target.id, target.col1, target.col2, target.col3) <> source.code and target.is_latest = 'Y'
Then
Update
Set scdstartdate = getdate()-1, is_latest = 'N'
When not matched by target
Then
Insert
(id, col1, col2, col3)
Values(source.id, source.col1, source.col2, source.col3)
When not matched by source
Then
Update
Set scdstartdate = getdate()-1, is_latest = 'N'
Output $action, source.id, source.col1, source.col2, source.col3, getdate(), '12/31/9999'
)
As changes
(
Action, Id, col1, col2, col3, scdstartdate, scdenddate
)
Where action = 'Update';
October 1, 2019 at 3:05 pm
This makes more sense! Your error message indicates a constraint violation on insert. That is probably happening on scdmain. It's probably source.id which would be NULL. Do you have a NOT NULL constraint on scdmain.Id?
October 1, 2019 at 5:44 pm
But why an Insert based error when all i am trying to do an update
October 1, 2019 at 5:54 pm
Because you're taking the output of your merge and inserting into scdmain. There are NULLs being inserted into scdmain whenever you have rows in target that are not matched by source.
Try this...temporarily remove your NOT NULL constraints from scdmain. Then, execute your merge statement. It should succeed.
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply