How to Insert a Data Table into SQL Server Database Table

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

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'

C# insert data from datatable to SQL Server database

"INSERT INTO tblTemp (FNCL_SPLIT_REC_ID, PROJ_ID, SALES_SRC_PRC) VALUES ("
                    + dt.Rows[i]["FNCL_SPLIT_REC_ID"].ToString().Trim() + ",'"
                    + dt.Rows[i]["PROJ_ID"].ToString().Trim() + "',"
                    + dt.Rows[i]["SALES_SRC_PRC"].ToString().Trim() + ");";

Removed the ' ' between FNCL_SPLIT_REC_ID as it is int and SALES_SRC_PRC since it is money.

Insert datatable in to sql table

 DataTable dt = new DataTable();
 string sql = "";
 for (int i = 0; i < dt2.Rows.Count; i++)
 {
    sql = sql + "insert into InvitationData (Col1, Col2, ColN) values('"
          + dt2.Rows[i]["columnname"].ToString().Trim() + "','"
          + dt2.Rows[i]["columnname"].ToString().Trim() + "','" 
          + dt2.Rows[i]["columnname"].ToString().Trim() + "')";
 }

From DataTable Insert into Sql Server Database

you can use uder-defined-table-type in SQL server to insert the DataTable to SQL Server in a smoother way.

create User-defined-table-type Example

use user-defined-Table-type in Procedure Example

How to Insert value to sql server database from data table vb.net

There are many problems in your code. Of course the immediate one that triggers the compilation error is the fact that you use command instead of command.CommandText to assign the query to the command, but there are others.

    If DataGridView1.Rows.Count > 0 Then
        Dim command As New Data.SqlClient.SqlCommand
        command.Text = "insert into MasbrgSpring 
                (KODEC, WIRE, DIMENSION, STD, NOMLOT, STAT) 
                 VALUES (@kodec, @wire,@dimension,@std,@nomlot,@stat)"
        command.Parameters.Add("@kodec", SqlDbType.VarChar)
        .... add all the other parameters ....

        ' Don't forget to set the connection to the command
        command.Connection = str
        str.Open()

        ' Now loop over the rows and for each one execute the insert command
        For i As Integer = 0 To DataGridView1.Rows.Count - 1
            command.Parameters(0).Value = DataGridView1.Rows(i).Cells(0).Value
            command.Parameters(1).Value = DataGridView1.Rows(i).Cells(1).Value
            .....    
            command.ExecuteNonQuery()
        Next
        str.Close()
    End If
End Sub

Here I have removed all the stuff about SqlDataAdapter, DataTable and DataSet. They are not needed at all when you execute directly a SqlCommand.

Finally the SqlCommand prepared in the loop is the one to be executed, if you create a new SqlCommand inside that SQLCOMMAND method then you need to give it the same info you have already given to the command used in the loop.

So there is no need also for SQLCOMMAND method but you can directly call ExecuteNonQuery on the prepared SqlCommand.

A final advice. It is a very bad programming practive to have a global SqlConnection object. It keeps resources locked on the client and on the server and if, for any reason, you get an exception, the connection remains open with side effects not immediately obvious when you get problems in other data code blocks.
ADO.NET has a Connection Pooling functionality far more advanced than the one you are trying to re-create keeping a global connection object. Instead use the Using statement around your Sql commands

Using sqlCon = New SqlConnection(......)
    If DataGridView1.Rows.Count > 0 Then
        Dim command As New Data.SqlClient.SqlCommand
        command.Connection = sqlCon
           ..... the previous code....
    End If
End Using

The using statement will help you to close and dispose the connection, while the connection pooling removes the overhead required to reconnect to the same database with the same connection string.

How to insert data table by stored procedure in SQL SERVER

To pass many rows efficiently to a stored procedure, use a table-valued parameter. The C# app can specify parameter type structured and a value of a data table. For maximum performance, make sure the DataTable column types match the server-side table type column types (including max length for string columns).

Below is an example excerpt from the documentation link above:

// Assumes connection is an open SqlConnection object.  
using (connection)  
{  
  // Create a DataTable with the modified rows.  
  DataTable addedCategories = CategoriesDataTable.GetChanges(DataRowState.Added);  

  // Configure the SqlCommand and SqlParameter.  
  SqlCommand insertCommand = new SqlCommand("usp_InsertCategories", connection);  
  insertCommand.CommandType = CommandType.StoredProcedure;  
  SqlParameter tvpParam = insertCommand.Parameters.AddWithValue("@tvpNewCategories", addedCategories);  
  tvpParam.SqlDbType = SqlDbType.Structured;  

  // Execute the command.  
  insertCommand.ExecuteNonQuery();  
}

Here are T-SQL snippets to create the table type and proc.

CREATE TYPE dbo.CategoryTableType AS TABLE  
    ( CategoryID int, CategoryName nvarchar(50) );
GO

CREATE PROC dbo.usp_InsertCategories
AS
@tvpNewCategories dbo.CategoryTableType READONLY
INSERT INTO dbo.Categories (CategoryID, CategoryName)  
    SELECT nc.CategoryID, nc.CategoryName FROM @tvpNewCategories AS nc;
GO

Even for trivial inserts, TVP performance can provide performance similar to SqlBulkCopy (many thousands per second) with the advantage of a stored procedure interface.

Insert entire DataTable into database at once instead of row by row?

I discovered SqlBulkCopy is an easy way to do this, and does not require a stored procedure to be written in SQL Server.

Here is an example of how I implemented it:

// take note of SqlBulkCopyOptions.KeepIdentity , you may or may not want to use this for your situation.  

using (var bulkCopy = new SqlBulkCopy(_connection.ConnectionString, SqlBulkCopyOptions.KeepIdentity))
{
      // my DataTable column names match my SQL Column names, so I simply made this loop. However if your column names don't match, just pass in which datatable name matches the SQL column name in Column Mappings
      foreach (DataColumn col in table.Columns)
      {
          bulkCopy.ColumnMappings.Add(col.ColumnName, col.ColumnName);
      }

      bulkCopy.BulkCopyTimeout = 600;
      bulkCopy.DestinationTableName = destinationTableName;
      bulkCopy.WriteToServer(table);
}

---

Related Topics