November 22, 2011 at 12:33 pm
Sam Bendayan (11/21/2011)
By "inserting in batches" do you mean breaking up the insert into batches? Such as doing 10,000 rows at a time instead of doing all 1 billion rows at once?If so, you can use a WHILE loop:
declare @SourceRowCount int
select @SourceRowCount = COUNT(*) FROM source_table
WHILE ((SELECT COUNT(*) FROM destination_table) < @SourceRowCount)
BEGIN
INSERT INTO destinationtable
SELECT TOP 10000 column_list FROM source_table
END
This code lacks any mechanism to SELECT . . . INSERT TOP 10000 rows that haven't already been inserted into destinationtable. If source_table has 100000 rows, this code could insert the same 10000 source_table rows 10 times over, all 100000 source_table rows, or any combination in between. I don't think that's what the OP wants!
Jason Wolfkill
November 22, 2011 at 12:40 pm
Tara-1044200 (11/18/2011)
Can someone help me doing insert as select * into tab1 in a batches. I have almost billion records to insert and i think inserting in batches would be best way.
Is it safe to assume there's an identity column or something similar on this?
If it is, just do something like this:
DECLARE @Current INT, @Final INT
SET @Current = 0
SELECT @Final = SELECT MAX(identitycolumn) FROM view
WHILE @Current < @Final
BEGIN
INSERT INTO tab1
SELECT *
FROMview
WHEREidentityColumn > @Current AND identityColumn <= @Current + 500000
SET @Current = @Current + 500000
END
With Dates, you can do something similar with date functions. You just need to find a way to break it up repeatably.
On a side note, I'm one of the local cheerleaders for SSIS. I wouldn't use it for this, not in the same database.
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
November 22, 2011 at 2:07 pm
Evil Kraig F (11/22/2011)
With Dates, you can do something similar with date functions. You just need to find a way to break it up repeatably.On a side note, I'm one of the local cheerleaders for SSIS. I wouldn't use it for this, not in the same database.
I would agree, except that in this case there doesn't appear to be anything to uniquely identify the rows that you can then key off of.
With SSIS - you would also have to be concerned with network traffic and/or memory utilization on the server.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply