Samuele E. Locatelli
42 lines
1.2 KiB
Transact-SQL
42 lines
1.2 KiB
Transact-SQL
|
|
-- =============================================
|
|
-- Author: S.E. Locatelli
|
|
-- Create date: 2013.11.29
|
|
-- Description: Procedura per import dati in ProductionLog da dati ProductionLogRaw
|
|
--
|
|
-- ATTENZIONE: dato l'intervallo temporale in ext.ProductionLogRaw eventuali dati precedenti in dbo.ProductionLog saranno eliminati
|
|
-- =============================================
|
|
CREATE PROCEDURE [ext].[stp_updateProductionLog]
|
|
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
-- Create a temporary table variable to hold the output actions.
|
|
DECLARE @deleted INT = 0
|
|
,@inserted INT = 0
|
|
,@fromDate DATETIME = GETDATE()
|
|
,@toDate DATETIME = GETDATE()
|
|
|
|
-- primo step: calcolo inizio/fine periodo...
|
|
SELECT @fromDate=MIN(EventStart), @toDate=MAX(EventStart) FROM ext.ProductionLogRaw
|
|
|
|
|
|
-- secondo step: elimino dal tracciato tutti i dati dal periodo indicato
|
|
DELETE FROM dbo.ProductionLog
|
|
WHERE EventStart BETWEEN @fromDate AND @toDate
|
|
|
|
SELECT @deleted = ISNULL(@@ROWCOUNT,0)
|
|
|
|
-- inserisco i nuovi dati
|
|
INSERT INTO dbo.ProductionLog
|
|
SELECT * FROM ext.ProductionLogRaw
|
|
|
|
SELECT @inserted = ISNULL(@@ROWCOUNT,0)
|
|
|
|
-- riporto in output quanto eliminato/inserito
|
|
SELECT @deleted as RecDeleted, @inserted as RecInserted
|
|
|
|
END
|
|
|