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,000s 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 arent too big, 100,000 could be handled with insert statements. But Id look into using bcp as thats what its for.

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

You could possibly use DTS (Data Transformation Services?). Ive used it from within SQL Server Enterprise Manager, but Ive 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 dont 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 dont 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