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

Asaf Gilad picture Asaf Gilad · Jan 31, 2012 · Viewed 244.2k times · Source

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

Navid Farhadi picture Navid Farhadi · Jan 31, 2012

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'