What is the best database engine independent way to load a huge amount of data.
While I'm using SQL Server I use SqlBulkCopy but want to untie from SQL Server
A:
Database independent bulk insert? Not possible.
The closest I can think of is to create an insert script with lines like this:
INSERT INTO TableName (...) VALUES (...);
It will compress well, so you might want to gzip it.
You're probably better off sticking with the database specific commands though. "Database independent" is often just a synonym for "slow".
Mark Byers
2010-02-13 22:00:05
+2
A:
There is no database engine independent way of doing this. Each DB server has its own way (e.g. bcp for Sybase).
There may be some 3rd party product which can do the work for you but it will merely be a wrapper around server-specific methods underneath (If that's what you're looking for, you may want to clarify your question).
NOTE: doing a bunch of INSERTs as Mark suggested is definitely not the same, since INSERTs are MUCH slower than native bulk inserts due to being logged (unlike bcp).
DVK
2010-02-13 22:13:17
Bulk inserts are also logged (and foreign keys checked, indexes updated, etc).
Pent Ploompuu
2010-02-13 22:25:58
bcp: - Does not call triggers - does not log individual inserts in fast mode on SQL server or on sybase.
DVK
2010-02-14 01:10:06
bcp might not do it but `SqlBulkCopy` does support triggers (if you so choose). And if your database is using the full recovery model, then even bcp operations are fully logged - http://msdn.microsoft.com/en-us/library/ms191244.aspx
Pent Ploompuu
2010-02-14 13:51:03