1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40
| update
dbo.BEN_BENEFICIARY
set
TO_UPDATE = 1,
FIRST_NAME = isnull(tmp.FIRST_NAME, '?'),
LAST_NAME = isnull(tmp.LAST_NAME, '?'),
ZIP_CODE = isnull(tmp.ZIP_CODE, dbo.Without()),
NEWSLETTER = dbo.BoolToString(isnull(tmp.NEWSLETTER, 0)),
ISVIP = dbo.BoolToString(isnull(tmp.ISVIP, 0)),
ACTIVE = dbo.BoolToString(isnull(tmp.ACTIVE, 0)),
BIRTH_DATE = dbo.ShortDate(tmp.BIRTH_DATE),
CIVILITY = isnull(tmp.CIVILITY, '?'),
BO_CREATION_DATE = dbo.ShortDate(tmp.BO_CREATION_DATE),
BO_UPDATE_DATE = dbo.ShortDate(tmp.BO_UPDATE_DATE),
CONTRACT_STARTING_DATE = dbo.ShortDate(tmp.CONTRACT_STARTING_DATE),
CONTRACT_END_DATE = dbo.ShortDate(tmp.CONTRACT_END_DATE),
PRO_EXTERNAL_ID = isnull(tmp.PRO_EXTERNAL_ID, dbo.DefaultProfileExtId()),
CUS_EXTERNAL_ID = isnull(tmp.CUS_EXTERNAL_ID, dbo.DefaultCustomerExtId()),
DEPARTEMENT = dbo.Departement(tmp.ZIP_CODE),
REGION = dbo.Region(tmp.ZIP_CODE),
UPDATE_DATE = @currentDate
from
dbo.BEN_BENEFICIARY img
inner join dbo.BEN_BENEFICIARY_TEMP tmp
on (img.EXTERNAL_ID = tmp.EXTERNAL_ID)
where
img.FIRST_NAME <> isnull(tmp.FIRST_NAME, '?')
or img.LAST_NAME <> isnull(tmp.LAST_NAME, '?')
or img.ZIP_CODE <> isnull(tmp.ZIP_CODE, dbo.Without())
or img.NEWSLETTER <> dbo.BoolToString(isnull(tmp.NEWSLETTER, 0))
or img.ISVIP <> dbo.BoolToString(isnull(tmp.ISVIP, 0))
or img.ACTIVE <> dbo.BoolToString(isnull(tmp.ACTIVE, 0))
or isnull(img.BIRTH_DATE, @currentDate) <> isnull(dbo.ShortDate(tmp.BIRTH_DATE), @currentDate)
or img.CIVILITY <> isnull(tmp.CIVILITY, '?')
or img.BO_CREATION_DATE <> dbo.ShortDate(tmp.BO_CREATION_DATE)
or img.BO_UPDATE_DATE <> dbo.ShortDate(tmp.BO_UPDATE_DATE)
or isnull(img.CONTRACT_STARTING_DATE, @currentDate) <> isnull(dbo.ShortDate(tmp.CONTRACT_STARTING_DATE), @currentDate)
or isnull(img.CONTRACT_END_DATE, @currentDate) <> isnull(dbo.ShortDate(tmp.CONTRACT_END_DATE), @currentDate)
or img.PRO_EXTERNAL_ID <> isnull(tmp.PRO_EXTERNAL_ID, dbo.DefaultProfileExtId())
or img.CUS_EXTERNAL_ID <> isnull(tmp.CUS_EXTERNAL_ID, dbo.DefaultCustomerExtId()) |
Partager