CREATE TABLE [TradingData].[Security](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Isin] [varchar](20) NOT NULL,
	[Ric] [varchar](20) NOT NULL,
	[Mic] [varchar](20) NULL,
	[Sedol] [varchar](20) NULL,
	[BftId] [int] NULL,
	[Name] [nvarchar](max) NULL,
	[LongName] [nvarchar](max) NULL,
	[ClassId] [int] NULL,
	[CurrencyId] [int] NULL,
	[EconomicSectorId] [int] NULL,
	[CountryId] [int] NOT NULL,
	[ParentId] [int] NULL,
	[ParentIdentify] [varchar](20) NULL,
	[ParentIdentifyType] [varchar](20) NULL,
 CONSTRAINT [PK_Security] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

CREATETRIGGER [TradingData].[IT_Security] ON [TradingData].[Security] INSTEAD OF INSERT AS
BEGIN
SET NOCOUNT OFF;

merge into [TradingData].[Security] as T
using inserted as i
on LOWER(T.Isin)= LOWER(i.Isin) OR  LOWER(T.Ric) = LOWER(i.Ric) OR  LOWER(T.Sedol) = LOWER(i.Sedol) OR  LOWER(T.Sedol) = LOWER(i.Sedol)

when matched then 
update set 
	T.Mic=i.Mic,
	T.Isin=i.Isin,
	T.Sedol=i.Sedol,
	T.Ric=i.Ric,
	T.Name=i.Name,
	T.LongName=i.LongName,
	T.ClassId=i.ClassId,
	T.CurrencyId=i.CurrencyId,
	T.EconomicSectorId=i.EconomicSectorId,
	T.CountryId=i.CountryId,
	T.ParentId=i.ParentId

when not matched then
INSERT
		(Isin,Ric,Sedol,Mic,BftId,Name,LongName,ClassId,CurrencyId,EconomicSectorId,CountryId,ParentId,ParentIdentify,ParentIdentifyType)
VALUES
		(i.Isin,i.Ric,i.Sedol,Mic,i.BftId,i.Name,i.LongName,i.ClassId,i.CurrencyId,i.EconomicSectorId,i.CountryId,i.ParentId,i.ParentIdentify,i.ParentIdentifyType);

select Id from  [TradingData].[Security] where @@ROWCOUNT > 0 and Id = scope_identity();
End