July 19, 2012 at 7:47 am
Hoping someone can help on this one, I have a developer who wants to batch load from one table in sets of ten at a time obviously not inserting the same thing twice.
I've created the below script as a test but wanted to know if theres a better way, the main problem is there isn't really anything which can be used uniquely to identify the rows in the new table so I can't really use subqueries with left joins, where clauses etc to eliminate the already inserted rows from the new table.
SELECT TOP 100 identity(int) [Id],ColumnName INTO #FRtemp
FROM dbo.TableName
DECLARE @MinLoadCounter INT, @MaxLoadCounter INT, @End INT
SET @End = (SELECT MAX(id+1) FROM dbo.#FRtemp)
SET @MinLoadCounter = (SELECT MIN(id) FROM dbo.#FRtemp)
SET @MaxLoadCounter = @MinLoadCounter + 4
SELECT @MinLoadCounter,@MaxLoadCounter
WHILE @MaxLoadCounter < @End
BEGIN
SELECT TOP 10 id
FROM #FRtemp
WHERE id between @MinLoadCounter and @MaxLoadCounter
SET @MinLoadCounter = @MaxLoadCounter+1
SET @MaxLoadCounter = @MaxLoadCounter+5
WAITFOR DELAY '00:00:02'
END
DROP TABLE #FRtemp
July 19, 2012 at 8:16 am
This should work but you have a couple issues. You said you want to load in batches of 10. You select top 10 but limit it to 5 rows with the Min/Max logic.
The biggest issue with your script is that if you don't have a multiple of 5 it will drop the last batch. Change your top 100 to top 101 and you will see what I mean. 😉
You can however make this a little simpler while also making it more accurate. Consider the following script.
SELECT TOP 121 identity(int) [Id], name INTO #FRtemp
FROM sys.sysobjects
DECLARE @MinLoadCounter INT, @End INT
SET @End = (SELECT MAX(id+1) FROM dbo.#FRtemp)
SET @MinLoadCounter = 1 --this should always start at 1, no need to select your initial value because it is an identity
WHILE @MinLoadCounter < @End
BEGIN
SELECT top 10 id
FROM #FRtemp
WHERE id between @MinLoadCounter and @End
order by id
SET @MinLoadCounter = @MinLoadCounter + 10
END
DROP TABLE #FRtemp
It now controls the batch size using the TOP operator. I included the order by so we know which rows we are getting. See if this will get what you need.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 19, 2012 at 8:22 am
Excellent thanks I'll take a look
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply