Ducv2fq9

1BEGIN TRY
2--- Copy the PK from the source table into the target table-------
3SET IDENTITY_INSERT Users..Users ON
4------------------------------------------------------------------
5-- Increment every 1000000 records. You can choose any number that works for you.
6DECLARE @Increment INT = 1000000,
7        @LastRecord_PK INT = 0,
8        @iRowCount INT = 0,
9        @BatchRowCount INT = 0,
10        @iCurrentRowCount INT = 0,
11        @FirstRec_PK INT = 0
12         
13-- Get the min of the PK minus 1. This way we can start the range from the first record.
14-- Make sure to choose the right source table for the MIN(PK)
15DECLARE @FirstRecord_PK AS INT=(SELECT
16    MIN(Users_PK)
17FROM
18    Users.dbo.Users) - 1
19 
20SELECT @FirstRecord_PK
21 
22-- Select the database for the table that records each INSERT
23USE Utilities
24IF NOT EXISTS(SELECT *
25            FROM Sys.Tables
26            WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[BatchProcess]'))
27 
28BEGIN
29CREATE TABLE [Utilities].[dbo].[BatchProcess](
30    [Batch_PK] [INT] IDENTITY(1,1) NOT NULL,
31    [RowCountTemp]  [INT] NULL,
32    [BatchRowCount] [INT] NULL,
33    [DateTimeEntered] [DATETIME] NULL,
34    [FirstRecord_PK] INT NOT NULL,
35    [LastRecord_PK] INT NOT NULL
36) ON [PRIMARY]       
37END
38ELSE
39BEGIN
40-- Remove all records from the previous process.
41TRUNCATE TABLE Utilities.dbo.BatchProcess
42END
43 
44-- Create an Error handler table if not found.
45IF NOT EXISTS(SELECT *
46            FROM Sys.Tables
47            WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[Errors]'))
48BEGIN
49CREATE TABLE [Utilities].[dbo].[Errors](
50    [Errors_PK] [INT] IDENTITY(1,1) NOT NULL,
51    [Error_No] [INT] NULL,
52    [ErrorMessage] [VARCHAR] (4000) NULL,
53    [ErrorLine] [VARCHAR] (50) NULL,
54    [Date_Time] [DATETIME] NULL,
55 ) ON [PRIMARY]
56END
57 
58--- Starting the time and that will be the first record in the BatchProcess table
59INSERT INTO Utilities.dbo.BatchProcess
60    (RowCountTemp,BatchRowCount,DateTimeEntered,FirstRecord_PK,LastRecord_PK)
61VALUES
62    (0,0,GETDATE(),@FirstRecord_PK,@LastRecord_PK)
63 
64USE Users
65WHILE EXISTS (SELECT TOP 1 Users_PK FROM Users.dbo.Users WHERE Users_PK > @LastRecord_PK)
66BEGIN
67 
68    -- Set the values for the counters, the first and last record.
69    SELECT @FirstRecord_PK = @FirstRecord_PK + 1,
70            @LastRecord_PK = @FirstRecord_PK + @Increment
71 
72    ------------------------------------------------------------------------------
73    --- In here you place your code to process your records ----------------------
74    --- Make sure to add the range for the PKs as shown below --------------------
75    ------------------------------------------------------------------------------           
76        --- Insert records into the target table
77    INSERT INTO Users..Users(
78           [Users_PK],
79           [First],
80           [Last],
81           [StreetNumber],
82           [StreetName],
83           [City],
84           [State],
85           [Zip],
86           [Phone],
87           [Date_Uploaded]
88           )
89    SELECT
90           [Users_PK],  
91           [First],
92           [Last],
93           [StreetNumber],
94           [StreetName],
95           [City],
96           [State],
97           [Zip],
98           [Phone],
99           SUBSTRING([Date_Uploaded],1,8) AS Date_Uploaded
100    FROM
101        Users.dbo.Users Ue
102    WHERE Ue.Users_PK BETWEEN @FirstRecord_PK AND @LastRecord_PK
103    ORDER BY [Ue].[Last],[Ue].[First]
104    ------------------------------------------------------------------------------
105 
106    -- Gets the number of rows that were processed per batch        
107    SET @iCurrentRowCount = @@ROWCOUNT
108    -- Gets the number of rows for all batches that were processed so far                                           
109    SET @iRowCount = @iRowcount + @iCurrentRowCount
110    -- Gets the number of rows per batch
111    SET @BatchRowCount = @iCurrentRowCount
112    -- Gets the first PK that was processed per batch
113    SET @FirstRec_PK = @FirstRecord_PK
114    -- Gets the next first PK for the next batch
115    SET @FirstRecord_PK = @LastRecord_PK
116    PRINT @iRowCount
117     
118    --- Keep track of each process
119    INSERT INTO Utilities.dbo.BatchProcess
120        (RowCountTemp,BatchRowCount,DateTimeEntered,FirstRecord_PK,LastRecord_PK)
121    VALUES
122        (@iRowCount,@BatchRowCount,GETDATE(),@FirstRec_PK,@LastRecord_PK)
123 
124END
125END TRY
126 
127-- Let's check for any error and if so let's log the error.
128BEGIN CATCH
129 
130INSERT INTO [Utilities].[dbo].Errors
131    (
132        Error_No,ErrorMessage,ErrorLine,Date_Time
133    )
134VALUES
135    (
136    ERROR_NUMBER(),ERROR_MESSAGE(),ERROR_LINE(),GETDATE()
137    )
138 
139SELECT
140    ERROR_NUMBER()      AS ErrorNumber ,
141    ERROR_SEVERITY()    AS ErrorSeverity ,
142    ERROR_STATE()       AS ErrorState ,
143    ERROR_PROCEDURE()   AS ErrorProcedure ,
144    ERROR_LINE()        AS ErrorLine ,
145    ERROR_MESSAGE()     AS ErrorMessage;
146END CATCH;