[sp_PDAP7_5_TfrmReklamationClipBoardImport_von_PDAP.sql]
-- =============================================
-- Author:		Mark Hausmann
-- Create date: 10.02.2020 
-- Description:	Protokoll aus Excel Tabelle über die Zwischenablage
-- Duplikate werden automatisch herausgefiltert
--
-- =============================================
--DROP PROCEDURE sp_PDAP7_5_TfrmReklamationClipBoardImport
--GO
CREATE PROCEDURE sp_PDAP7_5_TfrmReklamationClipBoardImport
(
@ID int,
@FILE varchar(max),
@FILE_NAME varchar(250),
@MA_ID int
)
AS
BEGIN
SET NOCOUNT ON;
IF OBJECT_ID('tempdb..##PDAP7_5_TfrmReklamationClipboardImport_CONTENT') IS NOT NULL
DROP TABLE ##PDAP7_5_TfrmReklamationClipboardImport_CONTENT
SELECT
ID
,REK_ID_	= @ID
,Data		= val
,FILE_NAME_	= @FILE_NAME
,MA_ID	    = @MA_ID
INTO ##PDAP7_5_TfrmReklamationClipboardImport_CONTENT
FROM
dbo.SplitString(@FILE, CHAR(13) + CHAR(10))


IF OBJECT_ID('tempdb..#SER') IS NOT NULL
DROP TABLE #SER 
SELECT 
REKS_REK_ID_ = REK_ID_,
REKS_EZ_ID_ = (SELECT TOP 1 REK_EZ_ID FROM REK_STM WHERE REK_ID = REK_ID_),
REKS_SER_NR_ = CAST( Data AS varchar(20) ),
*
INTO #SER
FROM
##PDAP7_5_TfrmReklamationClipboardImport_CONTENT
DELETE FROM #SER WHERE NULLIF(LTRIM(RTRIM(REKS_SER_NR_)),'') IS NULL
DELETE FROM #SER WHERE REKS_EZ_ID_ IS NULL
--Mögliche Duplikate entfernen
;WITH  numbered
AS ( SELECT   *
, row_number() OVER ( PARTITION BY  REKS_SER_NR_  ORDER BY   REKS_REK_ID_    ) AS nr
FROM     #SER
)
--SELECT * FROM numbered
DELETE FROM  numbered
WHERE   nr > 1
--SELECT * FROM #SER

MERGE REK_SER
USING #SER E
ON ISNULL(REK_SER.REKS_SER_NR,'') = ISNULL(E.REKS_SER_NR_,'') AND ISNULL(REK_SER.REKS_REK_ID,0) = ISNULL(E.REKS_REK_ID_,0)
WHEN NOT MATCHED BY TARGET THEN
INSERT (
REKS_REK_ID,
REKS_EZ_ID,
REKS_SER_NR
)
VALUES (
REKS_REK_ID_,
REKS_EZ_ID_,
REKS_SER_NR_
);
--SELECT * FROM REK_SER ORDER BY 1 DESC

RAISERROR('|Die Daten aus dem Dokument wurden importiert. Die Daten werden auf der Registerseite der Seriennummern angezeigt. |INFO',16,1)
END
/
