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    Someone said insert line by line another suggested bulk update    etc  what it better  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

User · Answer

I found that it was better to add to the table row by row if your table has a primary key. Inserting the entire table at once creates a conflict on the auto increment.

Here's my stored Proc

CREATE PROCEDURE dbo.usp_InsertRowsIntoTable
@Year       int,
@TeamName   nvarchar(50),
AS
INSERT INTO [dbo.TeamOverview]
(Year,TeamName)
VALUES (@Year, @TeamName);
RETURN

I put this code in a loop for every row that I need to add to my table:

insertRowbyRowIntoTable(Convert.ToInt16(ddlChooseYear.SelectedValue), name);

And here is my Data Access Layer code:

        public void insertRowbyRowIntoTable(int ddlValue, string name)
    { 
        SqlConnection cnTemp = null;
        string spName = null;
        SqlCommand sqlCmdInsert = null;

        try
        {
            cnTemp = helper.GetConnection();
            using (SqlConnection connection = cnTemp)
            {
                if (cnTemp.State != ConnectionState.Open)
                    cnTemp.Open();
                using (sqlCmdInsert = new SqlCommand(spName, cnTemp))
                {
                    spName = "dbo.usp_InsertRowsIntoOverview";
                    sqlCmdInsert = new SqlCommand(spName, cnTemp);
                    sqlCmdInsert.CommandType = CommandType.StoredProcedure;

                    sqlCmdInsert.Parameters.AddWithValue("@Year", ddlValue);
                    sqlCmdInsert.Parameters.AddWithValue("@TeamName", name);

                    sqlCmdInsert.ExecuteNonQuery();

                }
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            if (sqlCmdInsert != null)
                sqlCmdInsert.Dispose();

            if (cnTemp.State == ConnectionState.Open)
                cnTemp.Close();
        }

    }

User · Answer

I am giving a very simple code  which i used in my solution  I have the same problem statement as yours       SqlConnection con   connection string     new SqlConnection  Data Source   uid sa pwd sa123 database Example1    con Open    string sql    Create Table abcd     foreach  DataColumn column in dt Columns        sql          column ColumnName           nvarchar 50            sql   sql TrimEnd new char                   SqlCommand cmd   new SqlCommand sql  con   SqlDataAdapter da   new SqlDataAdapter cmd   cmd ExecuteNonQuery    using  var adapter   new SqlDataAdapter  SELECT   FROM abcd   con    using var builder   new SqlCommandBuilder adapter     adapter InsertCommand   builder GetInsertCommand    adapter Update dt      adapter Update ds Tables 0     Incase u have a data-set    con Close      I have given a predefined table-name as  abcd   you must take care that a table by this name doesn t exist in your database   Please vote my answer if it works for you

User · Answer

From my understanding of the question this can use a fairly straight forward solution Anyway below is the method i propose  this method takes in a data table and then using SQL statements to insert into a table in the database Please mind that my solution is using MySQLConnection and MySqlCommand replace it with SqlConnection and SqlCommand   public void InsertTableIntoDB CreditLimitSimple System Data DataTable tblFormat                for  int i   0  i  lt  tblFormat Rows Count  i                           String InsertQuery   string Empty               InsertQuery    INSERT INTO customercredit                                 ACCOUNT CODE NAME CURRENCY CREDIT LIMIT                                 VALUES       tblFormat Rows i   AccountCode   ToString             tblFormat Rows i   Name   ToString             tblFormat Rows i   Currency   ToString             tblFormat Rows i   CreditLimit   ToString                          using  MySqlConnection destinationConnection   new MySqlConnection System Configuration ConfigurationManager ConnectionStrings  ConnectionString   ToString                 using  var dbcm   new MySqlCommand InsertQuery  destinationConnection                                 destinationConnection Open                    dbcm ExecuteNonQuery                                   CreditLimit

User · Answer

best way to deal with this is sqlbulkcopy        but if you dont like it you can do it like this       read current sql table in an adapter       add rows of datatable   I have mentioned a simple way of it       and finally updating changes      Dim cnn As New SqlConnection  connection string               cnn Open       Dim cmd As New SqlCommand  select   from  sql server table   cnn      Dim da As New SqlDataAdapter cmd             Dim ds As New DataSet       da Fill ds   sql server table       Dim cb As New SqlCommandBuilder da                 for each datatable row     ds Tables  sql server table   Rows Add COl1  COl2       da Update ds   sql server table

User · Answer

public bool BulkCopy ExcelToSqlBo objExcelToSqlBo  DataTable dt  SqlConnection conn  SqlTransaction tx        int check   0      bool result   false      string getInsert           try               if  dt Rows Count  gt  0                        foreach  DataRow dr in dt Rows                                if  dr    null                                        if  check    0                                                getInsert    INSERT INTO  tblTemp   firstName   lastName   Father   Mother   Category                                        sub 1   sub LG2                                       select      dr 0  ToString             dr 1  ToString             dr 2  ToString             dr 3  ToString             dr 4  ToString   Trim             dr 5  ToString   Trim             dr 6  ToString                             check    1                                            else                                               getInsert      UNION ALL                             getInsert      select       dr 0  ToString             dr 1  ToString             dr 2  ToString             dr 3  ToString             dr 4  ToString   Trim             dr 5  ToString   Trim             dr 6  ToString                              check                                                                      result   common ExecuteNonQuery getInsert  DatabasesName  conn  tx                     else                       throw new Exception  No row for insertion                      dt Dispose              catch  Exception ex                dt Dispose            throw new Exception  Please attach file in Proper format               return result

User · Answer

If it s the first time for you to save your datatable  Do this  using bulk copy   Assure there are no PK FK constraint  SqlBulkCopy bulkcopy   new SqlBulkCopy myConnection     I assume you have created the table previously   Someone else here already showed how   bulkcopy DestinationTableName   table TableName  try                                                                     bulkcopy WriteToServer table                                          catch Exception e        messagebox show e message        Now since you already have a basic record  And you just want to check new record with the existing one  You can simply do this   This will basically take existing table from database  DataTable Table   new DataTable     SqlConnection Connection   new SqlConnection  ConnectionString      I assume you know better what is your connection string  SqlDataAdapter adapter   new SqlDataAdapter  Select   from     TableName  Connection    adapter Fill Table     Then pass this table to this function  public DataTable CompareDataTables DataTable first  DataTable second        first TableName    FirstTable       second TableName    SecondTable        DataTable table   new DataTable  Difference         try               using  DataSet ds   new DataSet                          ds Tables AddRange new DataTable     first Copy    second Copy                    DataColumn   firstcolumns   new DataColumn ds Tables 0  Columns Count                for  int i   0  i  lt  firstcolumns Length  i                                  firstcolumns i    ds Tables 0  Columns i                              DataColumn   secondcolumns   new DataColumn ds Table 1  Columns Count                for  int i   0  i  lt  secondcolumns Length  i                                  secondcolumns i    ds Tables 1  Columns i                              DataRelation r   new DataRelation string Empty  firstcolumns  secondcolumns  false                ds Relations Add r                for  int i   0  i  lt  first Columns Count  i                                  table Columns Add first Columns i  ColumnName  first Columns i  DataType                              table BeginLoadData                 foreach  DataRow parentrow in ds Tables 0  Rows                                DataRow   childrows   parentrow GetChildRows r                   if  childrows    null    childrows Length    0                      table LoadDataRow parentrow ItemArray  true                              table EndLoadData                          catch  Exception ex                throw ex             return table      This will return a new DataTable with the changed rows updated  Please ensure you call the function correctly  The DataTable first is supposed to be the latest   Then repeat the bulkcopy function all over again with this fresh datatable

User · Answer

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

User · Answer

I would suggest you go for bulk insert as suggested in this article    Bulk Insertion of Data Using C  DataTable and SQL server OpenXML function

[c#] How to insert a data table into SQL Server database table?

Examples related to c#

Examples related to sql-server

Examples related to datatable