Eliminare record non correlati

USE tempdb;
GO


-- creo le tabelle di prova

-- master
CREATE TABLE dbo.Headers
(
    IDHeader int NOT NULL
  , HeaderDate smalldatetime NOT NULL
  , CONSTRAINT PK_dboHeaders PRIMARY KEY CLUSTERED
  (
    IDHeader
  )
)
GO

-- detail
CREATE TABLE dbo.Details
(
    IDHeader int NOT NULL
  , IDDetail int NOT NULL
  , DetailName varchar(20) NOT NULL
  , CONSTRAINT PK_dboDetails PRIMARY KEY CLUSTERED
  (
      IDHeader
    , IDDetail
  )
)
GO

-- backup
CREATE TABLE dbo.tempDeleted
(
    IDHeader int NOT NULL
  , IDDetail int NOT NULL
  , DetailName varchar(20) NOT NULL
  , CONSTRAINT PK_dbotempDeleted PRIMARY KEY CLUSTERED
  (
      IDHeader
    , IDDetail
  )
)
GO

-- inserisco il master
INSERT INTO dbo.Headers (IDHeader, HeaderDate)
VALUES (1, '20100726 18:00')
INSERT INTO dbo.Headers (IDHeader, HeaderDate)
VALUES (2, '20100726 19:00')
INSERT INTO dbo.Headers (IDHeader, HeaderDate)
VALUES (3, '20100726 20:00')
-- manca il 4
INSERT INTO dbo.Headers (IDHeader, HeaderDate)
VALUES (5, '20100726 21:00')
GO


-- inserisco i dettagli "legati"
INSERT INTO dbo.Details (IDHeader, IDDetail, DetailName)
VALUES (1, 1, 'UNO_UNO')
INSERT INTO dbo.Details (IDHeader, IDDetail, DetailName)
VALUES (1, 2, 'UNO_DUE')
INSERT INTO dbo.Details (IDHeader, IDDetail, DetailName)
VALUES (2, 1, 'DUE_UNO')
INSERT INTO dbo.Details (IDHeader, IDDetail, DetailName)
VALUES (2, 2, 'DUE_DUE')
INSERT INTO dbo.Details (IDHeader, IDDetail, DetailName)
VALUES (3, 1, 'TRE_UNO')
INSERT INTO dbo.Details (IDHeader, IDDetail, DetailName)
VALUES (5, 1, 'CINQUE_UNO')
GO

-- inserisco i dettagli "slegati" (orfani)
INSERT INTO dbo.Details (IDHeader, IDDetail, DetailName)
VALUES (4, 1, 'QUATTRO_UNO')
INSERT INTO dbo.Details (IDHeader, IDDetail, DetailName)
VALUES (4, 2, 'QUATTRO_DUE')
GO



-- contenuto
SELECT * FROM dbo.Headers
/*
IDHeader    HeaderDate
----------- -----------------------
1           2010-07-26 18:00:00
2           2010-07-26 19:00:00
3           2010-07-26 20:00:00
5           2010-07-26 21:00:00
*/

SELECT * FROM dbo.Details
/*
IDHeader    IDDetail    DetailName
----------- ----------- --------------------
1           1           UNO_UNO
1           2           UNO_DUE
2           1           DUE_UNO
2           2           DUE_DUE
3           1           TRE_UNO
4           1           QUATTRO_UNO
4           2           QUATTRO_DUE
5           1           CINQUE_UNO
*/

-- cancello gli orfani e li metto in una tabella creata al volo per il backup
DELETE D
OUTPUT DELETED.* INTO dbo.tempDeleted
FROM dbo.Details    D
LEFT JOIN dbo.Headers  H  ON H.IDHeader = D.IDHeader
WHERE
  H.IDHeader IS NULL
GO

-- questo è il backup
SELECT * FROM dbo.tempDeleted

/*
IDHeader    IDDetail    DetailName
----------- ----------- --------------------
4           1           QUATTRO_UNO
4           2           QUATTRO_DUE
*/

-- clean up
DROP TABLE dbo.Headers
GO
DROP TABLE dbo.Details
GO
DROP TABLE dbo.tempDeleted
GO