How to insert a data table into SQL Server database table?

Question

I have imported data from some Excel file and I have saved it into a datatable. Now I'd like to save this information in my SQL Server database.

I saw a lot of information on the web but I cannot understand it:

1. Someone said insert line by line another suggested bulk update... etc: what it better?
2. Should I use OLE or SQL Server objects (like dataAdapter or connection)?

My need is to read the employee weekly hours report, from his Excel file and save it to a database table where all the reports are saved (updating the db with new records every week).

The Excel file contains reports only for the current week.

Answer 1

Create a User-Defined TableType in your database:

CREATE TYPE [dbo].[MyTableType] AS TABLE(     [Id] int NOT NULL,     [Name] [nvarchar](128) NULL ) 

and define a parameter in your Stored Procedure:

CREATE PROCEDURE [dbo].[InsertTable]     @myTableType MyTableType readonly AS BEGIN     insert into [dbo].Records select * from @myTableType  END 

and send your DataTable directly to sql server:

using (var command = new SqlCommand("InsertTable") {CommandType = CommandType.StoredProcedure}) {     var dt = new DataTable(); //create your own data table     command.Parameters.Add(new SqlParameter("@myTableType", dt));     SqlHelper.Exec(command); } 

To edit the values inside stored-procedure, you can declare a local variable with the same type and insert input table into it:

DECLARE @modifiableTableType MyTableType  INSERT INTO @modifiableTableType SELECT * FROM @myTableType 

Then, you can edit @modifiableTableType:

UPDATE @modifiableTableType SET [Name] = 'new value'