roberto
2/23/2018 - 10:56 AM

CSP

Load new currencies in table with not null but no identity

DECLARE @LastVal int;
SELECT @LastVal = max(CurrencyId) from dom.Currency;

truncate table stg.Currency;
insert into stg.Currency (CurrencyId, CurrencyCode)
select Nc.CurrencyId, Nc.CurrencyCode from (
select
 @LastVal + ROW_NUMBER() OVER(ORDER BY CurrencyCode ASC) AS CurrencyId
,S1.CurrencyCode
from (
	select distinct
	Er.CurrencyTo as CurrencyCode
	FROM stg.ExchangeRate Er
		except
	select distinct
	Cu.CurrencyCode as CurrencyCode
	from dom.Currency Cu
) S1
) Nc
;
insert into
dom.Currency (
 CurrencyId
,CurrencyCode
,CurrencyName
)
select
 CurrencyId
,CurrencyCode
,CurrencyName
from stg.Currency
;