March 2, 2013 at 2:04 pm
I have the specific insert query below, and it works fine if I have less than 20 records or so, but if I have millions of records to loop through,
it is not going to work. I have the batches below in hundreds, but i can be 1000, ,2000, etc..
Any idea how I can write this better WITHOUT using @@rowcount, and better performance would be very much appreciated.
DECLARE @min-2 INT;
DECLARE @max-2 INT;
DECLARE @min2 INT;
DECLARE @max2 INT;
SET @min-2 = 1;
SET @max-2 = 5;
IF @min-2 = 1 THEN
SET @min2 = 1, @max2 = 100;
ELSEIF @min-2 = 2 THEN
SET @min2 = 101, @max2 = 200;
ELSEIF @min-2 = 3 THEN
SET @min2 = 201, @max2 = 300;
ELSEIF @min-2 = 4 THEN
SET @min2 = 301, @max2 = 400;
ELSEIF @min-2 = 5 THEN
SET @min2 = 401, @max2 = 500;
END IF;
INSERT INTO table2
SELECT colum1, column2
FROM table1
WHERE id BETWEEN @min2 AND @max2;
END WHILE;
March 2, 2013 at 2:26 pm
what is the aim of what your doing? are you just inserting everything from one table into another in a batch of N as thats what it looks like to me?
you could run throuch a loop using select top N and inner joining on your tables to ensure the next batch dont already exist.
***The first step is always the hardest *******
March 3, 2013 at 1:20 am
I'm forced to agree with the above, this technique is usually best avoided if possible. What problem are you attempting to avoid by staggering your inserts like this?
However, as mentioned above (and slightly inaccurately, sorry, you want an outer join) the best bet is typically to use something like this, as long as you have some kind of business key you can rely on. Better would be to use a MERGE statement if you're updating from the System of Record to a warehouse and do inserts/updates simultaneously.
DECLARE @ROWCOUNT INT, @InsertVolume INT
SET @ROWCOUNT = 1
SET @InsertVolume = 5000 --Set to # of rows
WHILE @ROWCOUNT > 0
BEGIN
INSERT INTO TargetTable
SELECT TOP @InsertVolume
--ColumnList
FROM
staging
LEFT JOIN
Target
ON
staging.id = target.ID
WHERE
staging.id IS NULL
END
However, it doesn't look like you have any restrictors on your inserted data, it's just a blind dump from staging with staggered row insertions. Some more information on the process you're trying to do and why you're doing it would definately help us narrow down your best solution here.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply