Moving records consists of transferring them from one table, the source, to another table, the target. Neither SQL nor Transact-SQL directly supports this operation, which happens to be extremely easy. You have many options. Probably the easiest way to do this consists of copying the records from the source to the target, then deleting the same records from the source. Here is an example: using System; using System.Drawing; using System.Data; using System.Windows.Forms; using System.Data.SqlClient; public class Exercise : System.Windows.Forms.Form { Button btnMoveRecords; Button btnSelectRecords; DataGridView dgvEmployees; DataGridView dgvContractors; public Exercise() { InitializeComponent(); } void InitializeComponent() { btnMoveRecords = new Button(); btnMoveRecords.AutoSize = true; btnMoveRecords.Text = "Move Records"; btnMoveRecords.Location = new Point(12, 12); btnMoveRecords.Click += new EventHandler(btnMoveRecordsClick); dgvEmployees = new DataGridView(); dgvEmployees.Size = new Size(360, 200); dgvEmployees.Location = new Point(12, 44); dgvContractors = new DataGridView(); dgvContractors.Size = new Size(360, 100); dgvContractors.Location = new Point(12, 260); btnSelectRecords = new Button(); btnSelectRecords.Text = "Show Employees"; btnSelectRecords.AutoSize = true; btnSelectRecords.Location = new Point(110, 12); btnSelectRecords.Click += new EventHandler(btnSelectRecordsClick); Controls.Add(btnMoveRecords); Size = new Size(400, 415); Controls.Add(dgvEmployees); Controls.Add(dgvContractors); Text = "Exercise"; Controls.Add(btnSelectRecords); } void btnMoveRecordsClick(object sender, EventArgs e) { SqlConnection cntExercise = null; SqlCommand cmdExercise = null; using (cntExercise = new SqlConnection("Data Source=(local);" + "Database='Exercise1';" + "Integrated Security=yes;")) { cmdExercise = new SqlCommand( "INSERT INTO Employees " + "SELECT FirstName, LastName, ContractorCode, Salary " + "FROM Contractors " + "WHERE Contractors.Salary >= 10.00; " + "DELETE FROM Contractors " + "WHERE Contractors.Salary >= 10.00;", cntExercise); cntExercise.Open(); cmdExercise.ExecuteNonQuery(); MessageBox.Show("The Contractors who make more than $10 have been made Employees.", "Exercise", MessageBoxButtons.OK, MessageBoxIcon.Information); } } void btnSelectRecordsClick(object sender, EventArgs e) { using (SqlConnection cntExercise = new SqlConnection("Data Source=(local);" + "Database='Exercise1';" + "Integrated Security=yes;")) { SqlCommand cmdExercise = new SqlCommand("SELECT ALL * FROM Contractors;", cntExercise); cntExercise.Open(); cmdExercise.ExecuteNonQuery(); SqlDataAdapter sdaExercise = new SqlDataAdapter(cmdExercise); DataSet dsExercise = new DataSet("ContractorsSet"); sdaExercise.Fill(dsExercise); dgvContractors.DataSource = dsExercise.Tables[0]; } using (SqlConnection cntExercise = new SqlConnection("Data Source=(local);" + "Database='Exercise1';" + "Integrated Security=yes;")) { SqlCommand cmdExercise = new SqlCommand("SELECT ALL * FROM Employees;", cntExercise); cntExercise.Open(); cmdExercise.ExecuteNonQuery(); SqlDataAdapter sdaExercise = new SqlDataAdapter(cmdExercise); DataSet dsExercise = new DataSet("EmployeesSet"); sdaExercise.Fill(dsExercise); dgvEmployees.DataSource = dsExercise.Tables[0]; } } public static int Main() { System.Windows.Forms.Application.Run(new Exercise()); return 0; } } |
|
|
|