Samuele E. Locatelli
44 lines
1.4 KiB
SQL
44 lines
1.4 KiB
SQL
|
|
|
|
|
|
-- =============================================
|
|
-- Author: S.E. Locatelli
|
|
-- Create date: 2014.01.08
|
|
-- Description: Procedura per conversione e tipizzazione dati da tmp a ext x DiesGroupRaw
|
|
-- =============================================
|
|
CREATE PROCEDURE [tmp].[convertDiesGroupRaw]
|
|
(
|
|
@fileType NVARCHAR(50) = 'DiesGroupRaw'
|
|
)
|
|
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 = 'DiesGroupRaw'
|
|
BEGIN
|
|
-- svuoto tabella temp
|
|
TRUNCATE TABLE ext.DiesGroupRaw
|
|
|
|
-- inserisco con cast valori...
|
|
INSERT ext.DiesGroupRaw
|
|
WITH (TABLOCK)
|
|
( CodDiesGroup, Price, QtyTot, QtyEOL, ExpLife, EOLLife)
|
|
SELECT
|
|
CodDiesGroup,
|
|
CAST( CAST( REPLACE( '0' + REPLACE(Price,'.',''),',','.' ) AS float )AS decimal( 9,3 )) AS Price,
|
|
CAST(ISNULL(QtyTot,0) AS INT) AS QtyTot,
|
|
CAST(ISNULL(QtyEOL,0) AS INT) AS QtyEOL,
|
|
CAST( CAST( REPLACE( '0' + REPLACE(ExpLife,'.',''),',','.' ) AS float )AS decimal( 9,2 )) AS ExpLife,
|
|
CAST( CAST( REPLACE( '0' + REPLACE(EOLLife,'.',''),',','.' ) AS float )AS decimal( 9,2 )) AS EOLLife
|
|
FROM tmp.DiesGroupRaw;
|
|
|
|
SELECT COUNT(*) as RecordsIn FROM ext.DiesGroupRaw
|
|
END
|
|
|
|
END
|
|
|
|
|
|
|