Samuele E. Locatelli
45 lines
2.3 KiB
SQL
45 lines
2.3 KiB
SQL
-- =============================================
|
|
-- Author: Steamware
|
|
-- Mod. date: 2014.05.22
|
|
-- Description: Procedura per conversione e tipizzazione dati da tmp a ext x OrdersHistRaw
|
|
-- =============================================
|
|
CREATE PROCEDURE [tmp].[convertOrdersHistRaw]
|
|
(
|
|
@fileType NVARCHAR(50) = 'OrdersHistRaw'
|
|
)
|
|
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 = 'OrdersHistRaw'
|
|
BEGIN
|
|
-- svuoto tabella temp
|
|
TRUNCATE TABLE ext.OrdersHistRaw
|
|
|
|
-- inserisco con cast valori...
|
|
INSERT ext.OrdersHistRaw
|
|
WITH (TABLOCK)
|
|
( OrdNum, OrdRow, OrdDate, CodClient, CodItem, RawMat, RawMatCost, BatchQty, CodInco, OrdPrice, RawMatExtraCost, PriceOff, OrdQty, ProvvCost)
|
|
SELECT
|
|
OrdNum,
|
|
OrdRow,
|
|
dbo.f_dtFromRaw( OrdDate ) AS OrdDate,
|
|
CodClient,
|
|
CodItem,
|
|
RawMat,
|
|
CONVERT( DECIMAL( 18,6 ), CONVERT ( FLOAT , REPLACE( '0' + REPLACE(RawMatCost,'.',''),',','.' ) )) AS RawMatCost,
|
|
CONVERT(INT, CONVERT ( FLOAT , REPLACE( '0' + REPLACE(BatchQty,'.',''),',','.' ) )) AS BatchQty, -- lotto
|
|
CodInco,
|
|
CONVERT( DECIMAL( 18,6 ), CONVERT ( FLOAT , REPLACE( '0' + REPLACE(OrdPrice,'.',''),',','.' ) )) AS OrdPrice,
|
|
CONVERT( DECIMAL( 18,6 ), CONVERT ( FLOAT , REPLACE( '0' + REPLACE(RawMatExtraCost,'.',''),',','.' ) )) AS RawMatExtraCost,
|
|
CONVERT( DECIMAL( 9,6 ), CONVERT ( FLOAT , REPLACE( '0' + REPLACE(PriceOff,'.',''),',','.' ) )) AS PriceOff, -- trasformazione
|
|
CONVERT( INT, CONVERT ( FLOAT , REPLACE( '0' + REPLACE(OrdQty,'.',''),',','.' ) )) AS OrdQty,
|
|
CONVERT( DECIMAL( 9,6 ), CONVERT ( FLOAT , REPLACE( '0' + REPLACE(ProvvCost,'.',''),',','.' ) )) AS ProvvCost -- provvigioni
|
|
FROM tmp.OrdersHistRaw;
|
|
|
|
SELECT COUNT(*) as RecordsIn FROM ext.OrdersHistRaw
|
|
END
|
|
|
|
END |