locked
How to insert datatable rows into SQL table specified columns RRS feed

  • Question

  • User-1024101449 posted

    Hi,

    How to insert datatable rows into specified SQL table columns.

    For example,

    I have DataTable which contains 4 columns and 100 Rows.

    I have SQL Table called "Employee" which contains 10 columns and 500 Rows.

    However the matching column will be available in both tables (DataTable and SQL Table)

    I just want to insert the Datatable 4 columns into SQL table.

    something like below code.

    DataTable dt = new DataTable();

    string sql = "";

    for (int i = 0; i < dt2.Rows.Count; i++)

    {

    sql = sql + "insert into Employee (Col1, Col2, ColN) values('"
    +
    dt2.Rows[i]["columnname"].ToString().Trim() + "','"
    +
    dt2.Rows[i]["columnname"].ToString().Trim() + "','"
    +
    dt2.Rows[i]["columnname"].ToString().Trim() + "')";

    }

    So the final records would be 600 Rows..

    Note : remaining 6 columns cell may be blank or null.

    How to do this in C#.NET

    Thursday, June 21, 2018 12:30 PM

Answers

  • User-492460945 posted

    Hi gani,

    Please check the below code to insert simultaneously all the rows from data table to SQL Server.

     DataTable dt = new DataTable();
                SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["TestConnectionString"].ConnectionString);
                if (con.State == ConnectionState.Closed)
                    con.Open();
                using (con)
                {
                    using (SqlCommand cmd = con.CreateCommand())
                    {
                        cmd.CommandType = CommandType.Text;
                        
                        for (int i = 0; i < dt.Rows.Count; i++)
                            cmd.CommandText += "INSERT INTO TABLE (COL1, COL2, COL3) VALUES ('" + dt.Rows[i]["Col1"].ToString() + "','" + dt.Rows[i]["Col2"].ToString() + "','" + dt.Rows[i]["Col3"].ToString() + "'); ";
    
                        cmd.ExecuteNonQuery();
                    }
                }

    Thanks,

    RajeshV.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, July 9, 2018 12:01 PM

All replies