Inserting 100,000's of records into SQL Server 2k

[a_jam_sandwich]

Im currently writting an application for mailing and I need to insert 100,000's of records every few days into the SQL Server 2000 database.

 

Is there a very very fast way for me to insert these into the database application?

 

I know about the bcp.exe for importing but is there another way using .NET

 

Cheers

 

Andy

[Nerseus]

If the records aren't too big, 100,000 could be handled with insert statements. But I'd look into using bcp as that's what it's for.

 

I know of no way to automate bcp from .NET - it's just a command line utility.

 

You could possibly use DTS (Data Transformation Services?). I've used it from within SQL Server Enterprise Manager, but I've never tried to use the packages it creates from a programming language. There might be a COM interface to it - I doubt there is a pure .NET solution, but who knows?

 

DTS is similar to bcp. In fact, if you don't provide any scripting in the transform layer, it acts just about as fast as bcp.

 

-Nerseus

[JABE]

You can also use the BULK INSERT command from T-SQL if you don't like shelling out processes. Yes, you could also use DTS and it can be automated via COM but it may be too complex for a relatively simple task.

[a_jam_sandwich]

Cheers for the replys it is as I thourght so I did some tests

 

turns out the insert of 100,000 records can take place in 2.1 minuites so all is not too bad.

 

Andy