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

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

	SELECT 
		T.c.value('./IDEKMP[1]','varchar(8)') AS IDEKMP, 
		T.c.value('./OKTMO_P[1]','varchar(11)') AS OKTMO_P, 
		T.c.value('./SPEC_NAMES[1]','int') AS SPEC_NAMES, 
		T.c.value('./STAZH[1]','int') AS STAZH, 
		T.c.value('./EKS[1]','int') AS EKS, 
		T.c.value('./ZSL[1]','int') AS ZSL, 
		T.c.value('./KMO[1]','int') AS KMO, 
		T.c.value('./ZAK[1]','int') AS ZAK, 
		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(date,T.c.value('./DATEEND[1]', 'varchar(20)'),104) end AS DATEEND
	INTO #F044
	from @xml.nodes('/packet/zap') T(c)

	UPDATE t2 
		SET 
		 [STAZH]     = t1.[STAZH]
        ,[EKS]		 = t1.[EKS]
        ,[ZSL]		 = t1.[ZSL]
        ,[KMO]		 = t1.[KMO]
        ,[ZAK]		 = t1.[ZAK]
        ,[DATEBEG]	 = t1.[DATEBEG]
        ,[DATEEND]	 = t1.[DATEEND]
	FROM #F044 t1 
    JOIN [IES].[T_F044_SPEKMP] t2 on t1.IDEKMP = t2.IDEKMP and t1.OKTMO_P = t2.OKTMO_P and t1.SPEC_NAMES = t2.SPEC_NAMES

	INSERT INTO [IES].[T_F044_SPEKMP]
			   ([F044SpekmpID]
			   ,[IDEKMP]
			   ,[OKTMO_P]
			   ,[SPEC_NAMES]
			   ,[STAZH]
			   ,[EKS]
			   ,[ZSL]
			   ,[KMO]
			   ,[ZAK]
			   ,[DATEBEG]
			   ,[DATEEND])
	   SELECT  
				NEWID()
			   ,t1.[IDEKMP]
			   ,t1.[OKTMO_P]
			   ,t1.[SPEC_NAMES]
			   ,t1.[STAZH]
			   ,t1.[EKS]
			   ,t1.[ZSL]
			   ,t1.[KMO]
			   ,t1.[ZAK]
			   ,t1.[DATEBEG]
			   ,t1.[DATEEND]
	   FROM #F044 t1 
	   LEFT JOIN [IES].[T_F044_SPEKMP] t2 on t1.IDEKMP = t2.IDEKMP and t1.OKTMO_P = t2.OKTMO_P and t1.SPEC_NAMES = t2.SPEC_NAMES
	   WHERE t2.F044SpekmpID is null

	 DROP TABLE #F044

END
