asita wrote:Thank you DesNorton,
I got that, my question was how can I incorporate that in this single statement, that null values update always need to check for all columns, if source columns value is null or not, if so then no update on that column.
the reason is I will be having a column recordmodifieddate that need to be capture when there is any one column values is updated (in whole record) from src to target with some value change..
can you please advise
Thankyou in advance
asiti
You use 2 statements ...
Update existing records
UPDATE dest
SET dest.Value = ISNULL(src.Value, Dest.Value)
FROM SourceTable AS src
INNER JOIN DestinationTable AS dest ON src.Key = dest.key
Insert new records (see this link for a why http://source.entelect.co.za/why-is-this-upsert-code-broken )
INSERT INTO DestinationTable ( <<Fieldlist>> )
FROM SourceTable AS src
WHERE NOT EXISTS (SELECT 1 FROM DestinationTable AS dest WITH (XLOCK, HOLDLOCK)
WHERE desk.Key = src.Key);