August 6, 2014 at 4:02 pm
Hi,
I have a table with 200 millions of records. and I want then to insert into a different table. If am insert them all at a time then I'm coming up with insufficient buffer memory.
So I want to insert the records in batch wise.
Say 2million records Once and again 2million records second time.
Can somebody provide me the script for this please?
Thanks in advance.
August 6, 2014 at 5:02 pm
If the primary key is easily traversed (identity, datetime, etc.) you can easily loop through ranges of the primary key value, inserting a few thousand records at a time.
You can also bcp out the table and then bcp in, with some batch size. That of course requires disk space for the data file.
August 7, 2014 at 11:18 am
I am curious about "insufficient buffer memory" ...
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
August 7, 2014 at 11:53 am
Or you can use Import/Export wizard (or SSIS) to transfer the data in batches.
August 7, 2014 at 11:07 pm
may be below script you can use with change as per requirement.
DECLARE @C INT
DECLARE @I INT
DECLARE @L INT
DECLARE @max-2 INT
SET @C = 1
SET @I = 200000
SET @L = @C + @I
SET @max-2 =
(
SELECT COUNT(*)
FROM TEST1 A
)
SELECT @C AS C,
@I AS I,
@L AS L,
@max-2 AS MAX
BEGIN
INSERT INTO TEST2
(
ID,
Name
)
SELECT ID,
Name
FROM dbo.TEST1 A
WHERE (ID >= @C
AND ID < @L)
SET @C = @L
SET @L = @L + @I
END
August 7, 2014 at 11:25 pm
Thanks for all the replies/suggestions.
Megha: Thanks for the script....so in the script, are you referring ID as identity column...Coz...the table am using doesn't have an identity column...so I need to create an identity column first in table 1 I guess..right?
August 7, 2014 at 11:29 pm
You need to use primary key ..if it is sequential then it can be used ,else you need to generate identity i think.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply