if exists ( select * from sys.procedures p where p.name = 'sp_V023KsgLoad' ) DROP PROCEDURE [dbo].[sp_V023KsgLoad]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp_V023KsgLoad]
 @Xml xml
 ,@FileName varchar(50)
AS 
BEGIN

	select 
		 T.c.value('./K_KSG[1]', 'varchar(20)') AS K_KSG
		,T.c.value('./N_KSG[1]', 'varchar(500)') AS N_KSG
		,T.c.value('./IDUMP[1]', 'int') AS IDUMP
		,T.c.value('./KOEF_Z[1]', 'decimal(6,4)') AS KOEF_Z
		,case when T.c.value('./DATEBEG[1]', 'varchar(20)')='' then null else CONVERT(datetime,T.c.value('./DATEBEG[1]', 'varchar(20)'),104) end AS DATEBEG
		,case when T.c.value('./DATEEND[1]', 'varchar(20)')='' then null else CONVERT(datetime,T.c.value('./DATEEND[1]', 'varchar(20)'),104) end AS DATEEND
	into #V023
	from @Xml.nodes('/packet/zap') T(c)
	
	update t2 
	set  t2.N_KSG=t1.N_KSG
		,t2.KOEF_Z=t1.KOEF_Z
		,t2.DATEEND=t1.DATEEND
	from #V023 t1
	join [IES].[T_V023_KSG] t2 on (t1.K_KSG=t2.K_KSG and t1.IDUMP = t2.V006MedicalTerms and t1.DATEBEG = t2.DATEBEG)

	insert into [IES].[T_V023_KSG](V023KsgID,K_KSG,N_KSG,V006MedicalTerms,KOEF_Z,DATEBEG,DATEEND)
	select newid(),t1.K_KSG,t1.N_KSG,t1.IDUMP,t1.KOEF_Z,t1.DATEBEG,t1.DATEEND
	from #V023 t1
	left join [IES].[T_V023_KSG] t2 on (t1.K_KSG=t2.K_KSG and t1.IDUMP = t2.V006MedicalTerms and t1.DATEBEG = t2.DATEBEG)
	where t2.V023KsgID is null
		
	drop table #V023
END