------------------------------------------------------------------
DECLARE @PanelCommandTypes TABLE (
	[Code] [nvarchar](2) NOT NULL,
	[Name] [nvarchar](50) NOT NULL,
	[Description] [nvarchar](500) NOT NULL,
	[TotalLength] [int] NOT NULL,
	[CommandCodeName] [nvarchar](50) NULL,
	[guid] [uniqueidentifier] NOT NULL,
	[IsExcludedFromDisplay] [bit] not null
	)

insert into @PanelCommandTypes values ('PS', 'Phone Sequence', 'Phone Sequence', 84, 'PhoneSequence', 'B19D2A9F-78FF-47D7-9DBD-5E7F79FA529B' , 1);

MERGE INTO [dbo].PanelCommandTypes AS TARGET  
USING @PanelCommandTypes AS SOURCE  
ON TARGET.[guid] = SOURCE.[guid]
WHEN NOT MATCHED BY TARGET THEN
INSERT ([Code], [Name], [Description], [TotalLength], [CommandCodeName], [guid],[IsExcludedFromDisplay])
VALUES (SOURCE.[Code], SOURCE.[Name], SOURCE.[Description], SOURCE.[TotalLength], SOURCE.[CommandCodeName], SOURCE.[guid], SOURCE.[IsExcludedFromDisplay])
WHEN MATCHED THEN    
UPDATE SET 
target.[Code] = SOURCE.[Code], 
target.[Name] = SOURCE.[Name], 
target.[Description] = SOURCE.[Description], 
target.[TotalLength] = SOURCE.[TotalLength], 
target.[CommandCodeName]= SOURCE.[CommandCodeName], 
target.[guid]= SOURCE.[guid],
target.[IsExcludedFromDisplay]= SOURCE.[IsExcludedFromDisplay]


OUTPUT $action, SOURCE.*;

-----------------------------------------------------------

DECLARE @PanelCommandTypePanelTypes TABLE (
	[PanelCommandTypeId] [int] NOT NULL,
	[PanelTypeVersionRangeId] [int] NOT NULL
	)

insert into @PanelCommandTypePanelTypes ([PanelCommandTypeId] ,[PanelTypeVersionRangeId] )
select c.id,r.id
from PanelCommandTypes c, PanelTypeVersionRanges r
where c.[guid] in ('B19D2A9F-78FF-47D7-9DBD-5E7F79FA529B')
  and r.[guid] in ('9B493C41-A492-413C-96A9-6F867B8D0291','4C2FFB6F-B74B-424A-9808-F09D9FA3E8EB','23106F30-2500-49E9-A738-7E05C54C765E')

MERGE INTO [dbo].PanelCommandTypePanelTypes AS TARGET  
USING @PanelCommandTypePanelTypes AS SOURCE  
ON TARGET.[PanelCommandTypeId] = SOURCE.[PanelCommandTypeId] and target.paneltypeversionrangeid =source.paneltypeversionrangeid 
WHEN NOT MATCHED BY TARGET THEN
INSERT ([PanelCommandTypeId],[PanelTypeVersionRangeId])
VALUES (SOURCE.[PanelCommandTypeId], SOURCE.[PanelTypeVersionRangeId])
WHEN MATCHED THEN    
UPDATE SET 
target.[PanelCommandTypeId] = SOURCE.[PanelCommandTypeId], 
target.[PanelTypeVersionRangeId] = SOURCE.[PanelTypeVersionRangeId]


OUTPUT $action, SOURCE.*;



-----------------------------------------------------------------------
DECLARE @PanelCommandArguments TABLE (
	[CommandId] [int] NOT NULL,
	CommandArgumentName [nvarchar](50) NOT NULL,
	[Name] [nvarchar](50) NOT NULL,
	[Type] [nvarchar](50) NOT NULL,
	[StringLength] [int] NULL,
	[MinValue] [int] NULL,
	[MaxValue] [int] NULL,
	[DataSourceName] [nvarchar](100) NULL,
	[IsMandatory] [bit] NOT NULL,
	[Description] [nvarchar](500) NULL,
	[guid] [uniqueidentifier] NOT NULL
)


insert into @PanelCommandArguments values ((select id from PanelCommandTypes where guid='B19D2A9F-78FF-47D7-9DBD-5E7F79FA529B'),'Phone1','Phone1','String',19,null,null,null,1,null,'DDACC044-31AA-43F4-BA37-7255ECE43DFB');
insert into @PanelCommandArguments values ((select id from PanelCommandTypes where guid='B19D2A9F-78FF-47D7-9DBD-5E7F79FA529B'),'Phone2','Phone2','String',19,null,null,null,1,null,'7A036552-06CD-443E-95BA-9B5288A1D5DA');
insert into @PanelCommandArguments values ((select id from PanelCommandTypes where guid='B19D2A9F-78FF-47D7-9DBD-5E7F79FA529B'),'Phone3','Phone3','String',19,null,null,null,1,null,'49D3E19D-8108-4EC7-B7CA-1862955B81D3');
insert into @PanelCommandArguments values ((select id from PanelCommandTypes where guid='B19D2A9F-78FF-47D7-9DBD-5E7F79FA529B'),'Phone4','Phone4','String',19,null,null,null,1,null,'4237CAC7-6FB9-4840-B3B8-28783FD09210');

MERGE INTO [dbo].PanelCommandArguments AS TARGET  
USING @PanelCommandArguments AS SOURCE  
ON TARGET.[guid] = SOURCE.[guid]
WHEN NOT MATCHED BY TARGET THEN
INSERT ([CommandId],[Name],CommandArgumentName,[Type] ,[StringLength],[MinValue] ,[MaxValue] ,[DataSourceName],[IsMandatory] ,[Description] ,[guid])
VALUES (source.[CommandId],source.[Name],source.CommandArgumentName,source.[Type] ,source.[StringLength],source.[MinValue] ,source.[MaxValue] ,source.[DataSourceName],source.[IsMandatory] ,source.[Description],source.[guid])
WHEN MATCHED THEN    
UPDATE SET 
target.[CommandId]		=source.[CommandId],
target.[Name]			=source.[Name],
target.CommandArgumentName = source.CommandArgumentName,
target.[Type] 			=source.[Type] ,
target.[StringLength]	=source.[StringLength],
target.[MinValue] 		=source.[MinValue] ,
target.[MaxValue] 		=source.[MaxValue] ,
target.[DataSourceName]	=source.[DataSourceName],
target.[IsMandatory] 	=source.[IsMandatory] ,
target.[Description] 	=source.[Description] ,
target.[guid]			=source.[guid]

OUTPUT $action, SOURCE.*;