August 25, 2009 at 11:56 pm
Hi,
i am creating a huge table(arround 500 millions rows) using select into statement. My query select into the
new table using full outer join on three tables.
Problem is the query never got executed. I tried insert into (without a batch) the table get populated
, the query works but the whole insert statement would be one batch. If something goes wrong i would loose all
data.
I tried creating batch using filter on the joined field. The query was running for ever.
What is the best way to use batch while inserting into a huge table.
August 26, 2009 at 2:08 am
Hi ujjp
500 million rows are too much for one INSERT statement. Try to work in 100,000 or 500,000 row chunks.
If your table has a single column primary key it shouldn't be a large problem:
* Insert the first 100,000 source rows ordered by the primary key.
* Select the MAX of your PK column from the destination table
* Insert the next 100,000 source rows ordered by the PK where PK is greater than the previous max PK
* Select the MAX of your PK column from the destination table
* and so on...
Greets
Flo
August 26, 2009 at 6:35 am
Might want to try using SSIS.
I had one application where I needed to import around 15 million records into three separate tables. Originally I had it set up through a stored proc in SSMS, which took around 10 hours to run, and which would take exponentially longer the more data there was to insert.
Moving it to SSIS resulted in it taking only 15 minutes.
Keep in mind though, that using SSIS meant I had to use one of our main servers to run it, as opposed to my personal machine, since it was using around 12GB of memory to run the SSIS task.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply