Copia dati da tabella ad un'altra in automatico

USE tempdb;
GO

-- creo le tabelle
----------------------------------------------------------------------
CREATE TABLE #tabellaA
(
  ID int IDENTITY(1,1) Primary Key CLUSTERED,
  DataOra smalldatetime NOT NULL,
  Pippo tinyint NOT NULL,
  Pluto char(1) NOT NULL,
  Tizio int NOT NULL,
  Caio int NOT NULL,
  Sempronio int NOT NULL
)
GO

CREATE TABLE #tabellaB
(
  ID int IDENTITY(1,1) Primary Key CLUSTERED,
  DataOra smalldatetime NOT NULL,
  Caio int NOT NULL
)
GO
----------------------------------------------------------------------

-- creo la stored procedure
----------------------------------------------------------------------
CREATE PROCEDURE CopiaRecordInTabellaB
  @DataOra smalldatetime,
  @Pippo tinyint,
  @Pluto char(1),
  @Tizio int,
  @Caio int,
  @Sempronio int
AS
BEGIN

  -- disattivo i messaggi 
  SET NOCOUNT ON;

  -- apro la transazione per mantenere atomica l'operazione.
  -- nel caso in cui si verifichi un errore al suo interno, annullo tutto con rollback.

  BEGIN TRAN

    INSERT #tabellaA
    SELECT @DataOra, @Pippo, @Pluto, @Tizio, @Caio, @Sempronio

    -- inserisco anche su tabellaB
    INSERT #tabellaB
    SELECT @DataOra, @Caio

  COMMIT TRAN

END
----------------------------------------------------------------------
GO

-- la eseguo
----------------------------------------------------------------------
DECLARE @DataCorrente smalldatetime
SET @DataCorrente = GETDATE()

EXEC CopiaRecordInTabellaB @DataCorrente, 1, 'A', 1, 1, 1
EXEC CopiaRecordInTabellaB @DataCorrente, 2, 'B', 2, 2, 2
EXEC CopiaRecordInTabellaB @DataCorrente, 3, 'C', 3, 3, 3
EXEC CopiaRecordInTabellaB @DataCorrente, 4, 'D', 4, 4, 4
EXEC CopiaRecordInTabellaB @DataCorrente, 5, 'E', 5, 5, 5
GO
----------------------------------------------------------------------

-- ricavo i record inseriti
----------------------------------------------------------------------
SELECT 
  * 
FROM
  #tabellaA

SELECT 
  * 
FROM
  #tabellaB
----------------------------------------------------------------------

-- faccio pulizia
----------------------------------------------------------------------
DROP TABLE #tabellaA
DROP TABLE #tabellaB
DROP PROCEDURE CopiaRecordInTabellaB
----------------------------------------------------------------------

USE tempdb;

CREATE TABLE dbo.PaperFloat (
DateAndTime datetime NOT NULL,
Millitm smallint NOT NULL,
TagIndex smallint NOT NULL,
Val float NOT NULL,
[Status] tinyint NOT NULL,
Marker tinyint NOT NULL 
);

CREATE TABLE dbo.Paper (
DateAndTime datetime NOT NULL,
TagIndex smallint NOT NULL,
Val float NOT NULL 
);
GO

CREATE TRIGGER dbo.trI_PaperFloat
ON dbo.PaperFloat
FOR INSERT
AS
INSERT dbo.Paper
SELECT DateAndTime, TagIndex, Val
FROM INSERTED;
GO

INSERT dbo.PaperFloat VALUES('20070917 11:35', 0, 0, 135, 0, 0);
INSERT dbo.PaperFloat VALUES('20070917 11:35', 0, 1, 147, 0, 0);
INSERT dbo.PaperFloat VALUES('20070917 11:36', 0, 0, 139, 0, 0);
INSERT dbo.PaperFloat VALUES('20070917 11:36', 0, 1, 151, 0, 0);

/* Vediamo se il trigger è scattato */
SELECT *
FROM dbo.Paper;
GO

/* Output:

DateAndTime             TagIndex Val
----------------------- -------- ----------------------
2007-09-17 11:35:00.000 0        135
2007-09-17 11:35:00.000 1        147
2007-09-17 11:36:00.000 0        139
2007-09-17 11:36:00.000 1        151

(4 row(s) affected)

*/

/* OK, ora definiamo una vista che restituisce i valori richiesti */
CREATE VIEW dbo.vwPaper(DateAndTime, ValA, ValB)
AS
SELECT
      DateAndTime
    , MAX(CASE TagIndex
            WHEN 0 THEN Val
            ELSE 0
          END)
    , MAX(CASE TagIndex
            WHEN 1 THEN Val
            ELSE 0
          END)
FROM dbo.Paper
GROUP BY DateAndTime;
GO

/* Query */
SELECT *
FROM dbo.vwPaper;

/* Output:

DateAndTime             ValA                   ValB
----------------------- ---------------------- ----------------------
2007-09-17 11:35:00.000 135                    147
2007-09-17 11:36:00.000 139                    151

(2 row(s) affected)

*/

DROP VIEW dbo.vwPaper;
DROP TABLE dbo.PaperFloat, dbo.Paper;