Samuele E. Locatelli
64 lines
2.7 KiB
SQL
64 lines
2.7 KiB
SQL
|
|
|
|
|
|
-- =============================================
|
|
-- Author: S.E. Locatelli
|
|
-- Create date: 2013.11.28
|
|
-- Description: Procedura per conversioen e tipizzazione dati da tmp a ext x ProductionLogRaw
|
|
-- =============================================
|
|
CREATE PROCEDURE [tmp].[convertProductionLogRaw]
|
|
(
|
|
@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 ext.ProductionLogRaw
|
|
|
|
-- inserisco con cast valori...
|
|
INSERT ext.ProductionLogRaw
|
|
WITH (TABLOCK)
|
|
( 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 )
|
|
|
|
SELECT
|
|
dbo.f_dtFromRaw( EventStart ) AS EventStart, -- record nulli sono già stati cancellati
|
|
dbo.f_dtFromRaw( EventEnd ) AS EventEnd, -- record nulli sono già stati cancellati
|
|
dbo.f_trim(ISNULL(CodPlant ,'')),
|
|
dbo.f_trim(ISNULL(ProcessNum ,'')),
|
|
dbo.f_trim(ISNULL(NrPos ,'')),
|
|
dbo.f_trim(ISNULL(CodDies ,'')),
|
|
dbo.f_trim(ISNULL(CodClient ,'')),
|
|
dbo.f_trim(ISNULL(OrderNum ,'')),
|
|
dbo.f_trim(ISNULL(BatchNum ,'')),
|
|
dbo.f_trim(ISNULL(ProcessType,'')),
|
|
dbo.f_trim(ISNULL(EventType ,'')),
|
|
dbo.f_trim(ISNULL(RawMat ,'')),
|
|
dbo.f_trim(ISNULL(CodPackag ,'')),
|
|
CAST( CAST( REPLACE( '0' + ISNULL(QtyIN,'') ,',','.' ) AS float ) AS decimal( 18,4 )) AS QtyIN,
|
|
CAST( CAST( REPLACE( '0' + ISNULL(QtyOUT,''),',','.' ) AS float ) AS decimal( 18,4 )) AS QtyOUT,
|
|
CAST( CAST( REPLACE( '0' + ISNULL(QtyEXT,''),',','.' ) AS float ) AS decimal( 18,4 )) AS QtyEXT,
|
|
CAST( ISNULL(NumIN ,'') AS int )AS NumIN,
|
|
CAST( ISNULL(NumOUT ,'') AS int )AS NumOUT,
|
|
CAST( CAST( REPLACE( '0' + ISNULL(QuotaPlant,''),',','.' ) AS float )AS decimal( 9,4 )) AS QuotaPlant,
|
|
CAST( CAST( REPLACE( '0' + ISNULL(QuotaMan,''),',','.' ) AS float )AS decimal( 9,4 )) AS QuotaMan,
|
|
dbo.f_trim(ISNULL(Tags ,'')),
|
|
okPr,
|
|
okQM,
|
|
okSc,
|
|
okDi
|
|
FROM tmp.ProductionLogRaw;
|
|
|
|
SELECT COUNT(*) as RecordsIn FROM ext.ProductionLogRaw
|
|
END
|
|
|
|
END
|
|
|
|
|
|
|