Click here to Skip to main content
15,949,686 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more: , +
Hi we are using sql server 2008 r2, we new to this sql server 2008
we are facing one problem how to update bulk data in single statement
for example indestination table we have one identity column and one foreign key column and two data columns now out goal is to update data in that table by using primary key identity column(can't update) and rest of the fields updated.

now we are creating one source datatable with updated values and we are getting destination table primary identity rows in another table.

how to map source and destination tables. both tables have same schemas


thanks in advances
AG
Posted
Comments
kk_gp 22-Dec-11 22:33pm    
How to insert tabled data in to database.
We have one tabled data. That data should be inserted in to database.
The problem is both the table Ordinals are different and columns names are different.
I am using sql server 2008

1 solution

Try using the following :

using System;
using System.Data;
using System.Data.SqlClient;
   class PropagateChanges {
      static void Main(){
         string connString = "server=(local)\\SQLEXPRESS;
         database=MyDatabase; Integrated Security=SSPI";
         string query = @"select * from employees ";
         string update = @"update employees set firstname = @firstname where id = @id";
         SqlConnection conn = new SqlConnection(connString);
         try {
            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = new SqlCommand(query, conn);
            DataSet ds = new DataSet();   
            da.Fill(ds, "employees");
            DataTable dt = ds.Tables["employees"];
            dt.Rows[0]["firstname"] = "ABC";
            foreach (DataRow row in dt.Rows){
               Console.WriteLine(
                  "{0} {1}",
                  row["firstname"].ToString().PadRight(15),
                  row["lastname"].ToString().PadLeft(25));
            }
            // Update employees
            SqlCommand cmd = new SqlCommand(update, conn);
            cmd.Parameters.Add("@firstname",SqlDbType.NVarChar,15, "firstname");
            SqlParameter parm = cmd.Parameters.Add("@id",SqlDbType.Int,4,"id");
            parm.SourceVersion = DataRowVersion.Original;
            da.UpdateCommand = cmd;
            da.Update(ds, "employees");
         } catch(Exception e) {
            Console.WriteLine("Error: " + e);
         } finally {
            conn.Close();
         }
      }  
   }



Source Code : http://www.java2s.com/Code/CSharp/Database-ADO.net/UpdatetableusingSqlDataAdapter.htm[^]
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900