Samuele E. Locatelli
100 lines
4.4 KiB
SQL
100 lines
4.4 KiB
SQL
|
|
|
|
|
|
|
|
-- =============================================
|
|
-- Author: S.E. Locatelli
|
|
-- Create date: 2013.11.28
|
|
-- Description: Procedura per import dati in tabelal tmp da file csv
|
|
--
|
|
-- TRUNCATE TABLE tmp.ProductionLogRaw_KO
|
|
--
|
|
-- =============================================
|
|
CREATE PROCEDURE [tmp].[importProductionLogRaw]
|
|
(
|
|
@fileType NVARCHAR(50) = 'ProductionLogRaw'
|
|
)
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
-- per Giancarlo: rendere parametrico con esecuzione sql x poter inserire nei parametri il separatore ed il resto? http://www.sqlteam.com/article/using-bulk-insert-to-load-a-text-file
|
|
|
|
IF @fileType = 'ProductionLogRaw'
|
|
BEGIN
|
|
-- svuoto tabella temp
|
|
TRUNCATE TABLE tmp.ProductionLogRaw
|
|
|
|
-- carico dati bulk!
|
|
BULK INSERT tmp.ProductionLogRaw
|
|
FROM 'e:\test\ProductRaw.csv'
|
|
|
|
WITH (FIELDTERMINATOR = ';', ROWTERMINATOR = '\n', FIRSTROW = 2, ERRORFILE = 'e:\test\errorProduct.log')
|
|
--WITH (FIELDTERMINATOR = ';', ROWTERMINATOR = '\n', FIRSTROW = 2, ERRORFILE = 'e:\test\errorProduct.log', CODEPAGE = '1252')
|
|
|
|
-- ===============================================================================
|
|
-- pulisco l tabella dai record con chiave duplicata ( se doppi li elimino tutti )
|
|
-- ===============================================================================
|
|
SET XACT_ABORT ON;
|
|
BEGIN TRAN
|
|
|
|
-- salvo i dati con chiave duplicata
|
|
INSERT INTO tmp.ProductionLogRaw_KO
|
|
SELECT Prod.*, GETDATE() FROM tmp.ProductionLogRaw AS Prod
|
|
INNER JOIN
|
|
(
|
|
SELECT EventStart, EventEnd, CodPlant, ProcessNum, NrPos, CodDies
|
|
FROM tmp.ProductionLogRaw
|
|
GROUP BY EventStart, EventEnd, CodPlant, ProcessNum, NrPos, CodDies
|
|
HAVING COUNT(*)>1
|
|
) AS Dupl ON Prod.EventStart = Dupl.EventStart AND
|
|
Prod.EventEnd = Dupl.EventEnd AND
|
|
Prod.CodPlant = Dupl.CodPlant AND
|
|
Prod.ProcessNum = Dupl.ProcessNum AND
|
|
Prod.NrPos = Dupl.NrPos AND
|
|
Prod.CodDies = Dupl.CodDies
|
|
|
|
-- Cancello i record con chiave duplicata
|
|
DELETE Prod
|
|
WITH (TABLOCK)
|
|
FROM tmp.ProductionLogRaw AS Prod
|
|
INNER JOIN
|
|
(
|
|
SELECT EventStart, EventEnd, CodPlant, ProcessNum, NrPos, CodDies
|
|
FROM tmp.ProductionLogRaw
|
|
GROUP BY EventStart, EventEnd, CodPlant, ProcessNum, NrPos, CodDies
|
|
HAVING COUNT(*)>1
|
|
) AS Dupl ON Prod.EventStart = Dupl.EventStart AND
|
|
Prod.EventEnd = Dupl.EventEnd AND
|
|
Prod.CodPlant = Dupl.CodPlant AND
|
|
Prod.ProcessNum = Dupl.ProcessNum AND
|
|
Prod.NrPos = Dupl.NrPos AND
|
|
Prod.CodDies = Dupl.CodDies
|
|
|
|
-- ===============================================================================
|
|
-- pulisco la tabella dai record con date non corrette
|
|
-- ===============================================================================
|
|
DELETE FROM tmp.ProductionLogRaw
|
|
WITH (TABLOCK)
|
|
OUTPUT deleted.* INTO tmp.ProductionLogRaw_KO -- salvo i record cancellati in tabella KO
|
|
( EventStart, EventEnd, CodPlant, ProcessNum, NrPos, CodDies, CodClient, OrderNum, BatchNum, ProcessType, EventType, RawMat
|
|
, CodPackag, QtyIN, QtyOUT, QtyEXT, NumIN, NumOUT, QuotaPlant, QuotaMan, Tags, okPr, okQM, okSc, okDi )
|
|
WHERE EventStart >= EventEnd
|
|
OR CONVERT( BIGINT , EventStart ) + 68000000000000 <= EventEnd -- record che darebbero errore poi nel datediff > 68 anni
|
|
OR EventStart IS NULL
|
|
OR EventEnd IS NULL
|
|
|
|
-- SELECT * FROM tmp.ProductionLogRaw_KO
|
|
|
|
--ROLLBACK
|
|
COMMIT
|
|
|
|
SELECT COUNT(*) AS RecordsIn FROM tmp.ProductionLogRaw
|
|
|
|
END
|
|
END
|
|
|
|
|
|
|
|
|