-- =============================================
-- Author:		S.E. Locatelli
-- Create date: 2013.11.29
-- Description:	Procedura per conversioen e tipizzazione dati da tmp a ext x PackagRaw
-- =============================================
CREATE PROCEDURE [tmp].[convertPackagRaw]
(
	 @fileType NVARCHAR(50) = 'PackagRaw'
)
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 = 'PackagRaw'
		BEGIN
			-- svuoto tabella temp
			TRUNCATE TABLE ext.PackagRaw

			-- inserisco con cast valori...
			INSERT ext.PackagRaw
            WITH (TABLOCK)
            ( CodPackag, PackagDesc, RMCost, RMWeight, Tare, FullWeight ) 
            SELECT
                   CodPackag,
				   PackagDesc,
                   CAST( CAST( REPLACE( '0' + RMCost,',','.' )AS float )AS decimal( 9,3 ))  AS RMCost,
                   CAST( CAST( REPLACE( '0' + RMWeight,',','.' )AS float )AS decimal( 9,4 ))  AS RMWeight,
                   CAST( CAST( REPLACE( '0' + Tare,',','.' )AS float )AS decimal( 9,4 ))  AS Tare,
                   CAST( CAST( REPLACE( '0' + FullWeight,',','.' )AS float )AS decimal( 9,4 ))  AS FullWeight
            FROM tmp.PackagRaw;

			SELECT COUNT(*)  as RecordsIn FROM ext.PackagRaw
		END

END