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.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Insert Records

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 you

Lopaka

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 Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

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.
Go to Top of Page

LopakaB
Starting Member

22 Posts

Posted - 2008-04-11 : 16:23:34
I initially did the following loop:
*********************************
declare @i int
while @i < 1000000000
begin
insert into table(val) value(@i)
end
*********************************


Lopaka
Go to Top of Page

LopakaB
Starting Member

22 Posts

Posted - 2008-04-11 : 16:24:29
*** Forgot to add the increment in the loop

set @i = @i+1

sorry

Lopaka
Go to Top of Page

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 t1
cross join master.dbo.spt_values t2
cross join master.dbo.spt_values t3
--cross join master.dbo.spt_values t4

Add/remove cross joins as needed. Change tables as needed.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

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.
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -