Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
LopakaB
Starting Member
22 Posts |
Posted - 2008-04-10 : 20:31:15
|
What is the fastest way of inserting 1B rows into a table?I have tried a loop and it is slowing down and I estimate not finishing this month...:(Thank youLopaka |
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-04-10 : 20:41:49
|
Fastest methods I've seen is to CROSS JOIN large tables together to get the large result set.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-04-10 : 21:27:59
|
Is it from other sources like text files? I would do bulk-insert in chunk and not as a whole. |
|
|
LopakaB
Starting Member
22 Posts |
Posted - 2008-04-11 : 16:23:34
|
I initially did the following loop:*********************************declare @i intwhile @i < 1000000000begininsert into table(val) value(@i)end*********************************Lopaka |
|
|
LopakaB
Starting Member
22 Posts |
Posted - 2008-04-11 : 16:24:29
|
*** Forgot to add the increment in the loopset @i = @i+1sorryLopaka |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-04-11 : 16:35:21
|
Use a query like this to generate the large record set:select *from master.dbo.spt_values t1cross join master.dbo.spt_values t2cross join master.dbo.spt_values t3--cross join master.dbo.spt_values t4Add/remove cross joins as needed. Change tables as needed.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2008-04-11 : 17:42:51
|
Just to follow up on Tara's response; you can use the cross join method to generate a bunch of rows. But, I would suggest that you limit that to some number (million, 10 million, 100 million) depending on your system and use a loop to chunk in the inserts so you don't blow tempdb/tranlog. |
|
|
LopakaB
Starting Member
22 Posts |
Posted - 2008-04-11 : 18:05:04
|
Thank you, i created the initial billion records quickly...:) I calculated it would be next week for a loop to finish. Other option would be to write it in c/vb to create ascii table. Lopaka |
|
|
|
|
|