Hi, I was wondering what's the fastest way to make huge, non-computationally intensive inserts to a flat table. Some things I'm already doing: dropping indexes and constraints, using a stored procedure for insertion. Right now, the queries are executing one at a time, although I have heard about "batch inserting". As I understand, you just surround the batched insert statements or stored procedures with the "GO" command. However, I will be calling an "InsertAZillionRowsAtOnce" stored procedure from code, after programmatically calculating what to insert for many rows at one time. So, I can realistically only pass in a few arguments and can only execute a stored procedure once. Specific Questions : 1) What data-type should I be passing to the stored procedure so that by executing one stored procedure I can actually batch/bulk insert many, many rows--an array/a table/? 2) Should I be inserting directly into the table, or first into the TempDB or a table variable, and then merging that TempDB/table variable into the actual table? 3) Does anyone know of the syntax to do this from C#, or even just where to find the syntax? (preferably using ADO.NET, but I'll take what I can get) Thanks SCHEMA: Table contains 3 integer fields, 1 bigint field (primary key), and 1 bit field.
If I am not mistaken... BCP/BULK INSERT or SSIS/DTS (fast load) is the best way to make the insert faster... If you are not using these then it is advisable to use SELECT INTO instead of INSERT INTO... I don't know much about c# but check IRowsetFastload interface...
Thanks for the tip. That's exactly what I ended up doing (SqlBulkCopy), resulted in a roughly 20x speed increase.