February 16, 2023 at 3:38 am
Trust me, I donβt use format specifically because of your previous remarks. I changed as little as possible to illustrate a point.
Ah... now I understand.Β Heh... I thought "WTH?Β Is this the same Ed B that I'm thinking of"? π π π
--Jeff Moden
Change is inevitable... Change for the better is not.
July 31, 2025 at 7:58 pm
I was blowing out the log files trying to update 150M records & decided to do batch updates instead.
Here's a demo of the script I wrote to generate the commands I needed based on a batch size I thought was reasonable (1 M).Β I'm sure people smarter than me here will have better suggestions but this worked for me.
-- [ID] is the primary key in the source table #bigtable
-- @y is my 'batch processing' guide table
-- @commands is the table I'm going to loop through
/* PREP */
/*
DROP TABLE IF EXISTS #bigtable;
CREATE TABLE #bigtable (
[ID] VARCHAR(20) PRIMARY KEY
,[HatSize] TINYINT NULL)
INSERT INTO #bigtable ([ID], [HatSize])
SELECT CONCAT(pos1.[ch], pos2.[ch], pos3.[ch])
,CASE
WHEN pos1.[ch] IN ('A','B','C','D','E','F','G') THEN 6
WHEN pos1.[ch] IN ('H','I','J','K','L','M','N') THEN 7
WHEN pos1.[ch] IN ('O','P','Q','R','S','T','U') THEN 8
WHEN pos1.[ch] IN ('V','W','X','Y','Z') THEN 9
ELSE 10
END
FROM (SELECT CHAR([value]) AS [ch] FROM GENERATE_SERIES(65, 90, 1)) pos1
CROSS JOIN
(SELECT CHAR([value]) AS [ch] FROM GENERATE_SERIES(65, 90, 1)) pos2
CROSS JOIN
(SELECT CHAR([value]) AS [ch] FROM GENERATE_SERIES(65, 90, 1)) pos3
*/
/* Hold on to your hats, folks! */
DECLARE @batch INT = 40
DROP TABLE IF EXISTS #x;
CREATE TABLE #x (
[RecID] INT IDENTITY(1, 1)
,[ID] VARCHAR(20) NOT NULL PRIMARY KEY)
DECLARE @y TABLE (
[LowID] VARCHAR(20) NOT NULL
,[HighID] VARCHAR(20) NOT NULL
,[BatchID] TINYINT NOT NULL PRIMARY KEY)
DECLARE @commands TABLE (
[RecID] INT IDENTITY(1, 1)
,[Command] NVARCHAR(MAX) NOT NULL)
INSERT INTO #x ([ID])
SELECT [ID]
FROM #bigtable
;WITH range_cte ([RecID], [ID], [LowHighFlag], [NextVal]) AS (
SELECT [RecID]
,[ID]
,[RecID] % @batch
,LEAD([ID]
,1
,(SELECT MAX([ID]) FROM #x)) OVER (ORDER BY [ID])
FROM #x
WHERE [RecID] % @batch = 1
OR [RecID] % @batch = 0)
INSERT INTO @commands ([Command])
SELECT CONCAT('SET NOCOUNT ON; BEGIN TRAN; UPDATE #bigtable SET [HatSize] = [HatSize] + 10 WHERE [ID] BETWEEN '
,CHAR(39),CONVERT(VARCHAR(19), [ID]),CHAR(39)
,' AND '
,CHAR(39),CONVERT(VARCHAR(19), [NextVal]),CHAR(39)
,'; COMMIT TRAN; SET NOCOUNT OFF;')
FROM range_cte
WHERE [LowHighFlag] = 1
ORDER BY [RecID];
SELECT [Command]
FROM @commands
DROP TABLE IF EXISTS #x;
--DROP TABLE IF EXISTS #bigtable;
Viewing 2 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply