how to bulk insert into existing database table using ado.net 3.5

Question

Hi,

I have a table in database which already have 100 records. Now I have to insert 100 more records to it. Now which object we have to use? whether dataadapter or sqlbulkcopy?

Answer 1

If the underlying ADO.Net provider supports SQL batching you can use the DataAdapter. If you are using SQL server you can use the SqlBulkCopy. SqlBulkCopy has speed advantages as long as you can directly insert data into the database but only works with SQL server. If you need to use custom inserts, stored procs, or other data providers you will need to use the DataAdapter. The .Update method on the DataAdapter does run in a batch mode by default that ships many records to the database at a time is does the inserts on the server instead of looping the records one at a time on the client.

Keep in-mind that some providers do not support batch modes as all.

Also for loading data you might also look into SQL Server Intergration Services (SSIS)

Edit:

anishmarokey also makes a very good point. For processing 100 records it doesn't really matter how you do the inserts. Even with looping the records and doing custom SQL INSERTs will run reasonably fast.

Answer 2

I think sqlbulkcopy is not needed. sqlbulkcopy is to insert bulk(millions) data .

Note : sqlbulkcopy - The .NET Framework and .NET Compact Framework do not support all versions of every platform

Answer 3

I'll assume two things: that you are working with SQL Server and that you are Inserting your records from an external file. Here is a sample of the T-SQL code you need - it is quite simple (enter it right in a SQL prompt window in Mgmnt Studio):

BULK INSERT TableName
        FROM 'C:\SomeDirectory\SomeFile.txt'
            WITH
    (
                FIELDTERMINATOR = ',',
                ROWTERMINATOR = '\n'
    )
GO

Whether 100 records or 1 million, this is easy enough that there is no reason not to use Bulk Insert when you want to import from a CSV file. If the CSV doesn't match your table structure, then (what I do is) create a temporary table, do the import and then use an INSERT (as shown below) to copy from the temporary table to your target table, selecting just the fields or order that you need.

Just in case - if you are inserting 100 more records from another table or query, the syntax is:

Insert Into [dbName].[dbo].[TableName]
  Select Field1, Field2...FieldN From OtherTableName Where {Some conditions}

If your insert will include an Identity field it is:

Set IDENTITY_INSERT TableName ON
GO

Insert Into [dbName].[dbo].[TableName] (Field1, Field2...FieldN)
  Select Field1, Field2...FieldN From OtherTableName Where {Some conditions}
GO

Set IDENTITY_INSERT TableName OFF
GO

Note that you have to explicitly include the field list on the insert if you have an identity field where you do not have to do this otherwise.