Thursday, 17 October 2013

SQL Copy data in batches

-- SOURCE TABLE
DECLARE @Table1 TABLE
(
  AsOf DATETIME,
  Company VARCHAR(50),
  Name VARCHAR(50)
)
 
INSERT INTO @Table1
SELECT '20130101','Company1','John'
UNION SELECT '20130102','Company2','Tom'
UNION SELECT '20130101','Company3','Peter'
UNION SELECT '20130102','Company4','Ian'
 
-- DESTINATION TABLE
DECLARE @Table2 TABLE
(
  AsOf DATETIME,
  Company VARCHAR(50),
  Name VARCHAR(50)
)
 
-- BATCH TABLE
DECLARE @Batches TABLE
(
  AsOf DATETIME
)
 
INSERT INTO @Batches
SELECT DISTINCT AsOf FROM @Table1
 
-- COPY IN BATCHES
DECLARE @AsOf DATETIME
WHILE (Exists(SELECT 1 FROM @Batches))
BEGIN TRY  
  BEGIN TRAN   
    SELECT @AsOf = MIN(AsOf) FROM @Batches
    PRINT CONVERT(VARCHAR(20),GETDATE(),20) + ',' + 'Copying data for: '+convert(VARCHAR(8),@AsOf,112)
 
    INSERT INTO @Table2 (
      AsOf,
      Company,
      Name
    )
    SELECT
      AsOf,
      Company,
      Name
    FROM @Table1
    WHERE AsOf = @AsOf
 
    DELETE FROM @Batches WHERE AsOf = @AsOf
  COMMIT TRAN 
END TRY
BEGIN CATCH 
  ROLLBACK
END CATCH
 
-- SHOW RESULTS
SELECT * FROM @Table2

No comments:

Post a Comment