Importing 50,000 records on SQL server

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)

Importing 50,000 records on SQL server

Author

Topic

Naveed88
Starting Member

19 Posts

Posted - 2009-11-18 : 08:19:56

hi all,

i need here to import a bulk data,
from csv file to sql table.

i can use "Bulk Insert" from any file
to SQL table,

but problem is that i have to manipulate each record and

then insert into their receptive tables.

I have done all this
and my system works with up to 1000 records
in 100-100 records in each batches..

so it takes 10 rounds to complete...........

but I have to Upload 50,000 at a time
csv containing these much record is about 7-MB in size

But
**System.ArgumentNullException: Value cannot be null. Parameter name: collection at System.ThrowHelper.ThrowArgumentNullException(ExceptionArgument argument) at System.Collections.Generic.List`1.InsertRange(Int32 index, IEnumerable`1 collection) at ImportInventory.btnSaveSingPairPar_Click(Object sender, EventArgs e)" error occured.**

error comes

then my question is

How do i increase Connection efficiency
or
Is there any another method to upload bulk data
or
Is there any special way to connect sql and then start bulk uploading
or
Any suggestion would be appriciated

(¨`·.·´¨) Always
`·.¸(¨`·.·´¨) Keep
(¨`·.·´¨)¸.·´ Smiling!
`·.¸.·´ & Programming
Regards....
"Deevan" [Naveed Anjum]
Web Developer,9867374437-Mumbai.4

toddbkc
Starting Member

9 Posts

Posted - 2009-11-18 : 12:03:58

I have a couple of ideas for you...

One of them is to load the data into a staging table - once you get the data there, then you can insert into all of the underlying tables from that point. This can help you avoid Nullability errors like the one you are getting.

Option 2 is to use Integration Services. With this tool, you can have much better throughput than using Bulk Insert. It is more complex, but in some aspects, can be easier to understand.

TODD

Naveed88
Starting Member

19 Posts

Posted - 2009-11-18 : 23:19:44

hi TODD,

thanks for precious suggetions
let me check these options and
i will let u know

thanks again

(¨`·.·´¨) Always
`·.¸(¨`·.·´¨) Keep
(¨`·.·´¨)¸.·´ Smiling!
`·.¸.·´ & Programming
Regards....
"Deevan" [Naveed Anjum]
Web Developer,9867374437-Mumbai.4

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources