Samuele E. Locatelli
52 lines
1.8 KiB
Transact-SQL
52 lines
1.8 KiB
Transact-SQL
|
|
-- =============================================
|
|
-- Author: S.E. Locatelli
|
|
-- Create date: 2013.12.02
|
|
-- Description: Procedura per import dati in PackagDet da dati ProductionLogRaw/PackagRaw
|
|
-- =============================================
|
|
CREATE PROCEDURE [ext].[stp_mergeRawMat]
|
|
(
|
|
@source NVARCHAR(20) = 'ProductionLogRaw' -- nome della tabella sorgente tra ProductionLogRaw e PackagRaw
|
|
)
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
-- Create a temporary table variable to hold the output actions.
|
|
DECLARE @SummaryOfChanges TABLE(Change VARCHAR(20));
|
|
|
|
-- import con MERGE... verifico tipo di caricamento richiesto!
|
|
IF @source = 'ProductionLogRaw' -- carico da tracciato produzione
|
|
BEGIN
|
|
MERGE INTO RawMatDet AS Target
|
|
USING (SELECT DISTINCT RawMat FROM ext.ProductionLogRaw)
|
|
AS Source
|
|
ON Target.RawMat = Source.RawMat
|
|
--WHEN MATCHED THEN
|
|
-- UPDATE SET PackagName = Source.PackagName
|
|
WHEN NOT MATCHED BY TARGET THEN
|
|
INSERT (RawMat, ProcCost, ProcYield, CSR) VALUES (RawMat, 0, 0, 0)
|
|
OUTPUT $action INTO @SummaryOfChanges;
|
|
|
|
END
|
|
--ELSE IF @source = 'PackagRaw' -- carico da anagrafica
|
|
-- BEGIN
|
|
-- MERGE INTO PackagDet AS Target
|
|
-- USING (SELECT CodPackag, PackagDesc, RMCost, RMWeight, Tare, FullWeight FROM ext.PackagRaw)
|
|
-- AS Source
|
|
-- ON Target.CodPackag = Source.CodPackag
|
|
-- WHEN MATCHED THEN
|
|
-- UPDATE SET PackagDesc = Source.PackagDesc, RMCost = Source.RMCost, RMWeight = Source.RMWeight, Tare = Source.Tare, FullWeight = Source.FullWeight
|
|
-- WHEN NOT MATCHED BY TARGET THEN
|
|
-- INSERT (CodPackag, PackagDesc, RMCost, RMWeight, Tare, FullWeight) VALUES (CodPackag, PackagDesc, RMCost, RMWeight, Tare, FullWeight)
|
|
-- OUTPUT $action INTO @SummaryOfChanges;
|
|
|
|
-- END
|
|
|
|
-- Query the results of the table variable.
|
|
SELECT Change, COUNT(*) AS CountPerChange
|
|
FROM @SummaryOfChanges
|
|
GROUP BY Change;
|
|
END
|
|
|