ADO.NET How To: Copy Records

	ADO.NET How To: Copy Records

Introduction

Imagine you have two tables that are supposed to hold the same values. Maybe the tables were created by different people for the same goal. Maybe there is an old table that holds the records from previous business transactions and there is a new table with the new records. At one time, you may want to merge these records. You have various options.

Consider the following tables:

using System;
using System.Drawing;
using System.Data;
using System.Windows.Forms;
using System.Data.SqlClient;

public class Exercise : System.Windows.Forms.Form
{
    Button btnDatabase;

    public Exercise()
    {
        InitializeComponent();
    }

    void InitializeComponent()
    {
        btnDatabase = new Button();
        btnDatabase.Text = "Database";
        btnDatabase.Location = new Point(12, 12);
        btnDatabase.Click += new EventHandler(btnDatabaseClick);

        Controls.Add(btnDatabase);
    }

    void CreateDatabase()
    {
        using (SqlConnection connection =
            new SqlConnection("Data Source=(local);" +
                              "Integrated Security=SSPI;"))
        {
            SqlCommand command =
                new SqlCommand("CREATE DATABASE JacksonPetroleum " +
                               "ON PRIMARY " +
                               "( NAME = CompanyArchives, FILENAME = 'C:\\exercises\\jp.mdf') " +
                               "LOG ON " +
                               "( NAME = CommonLog, FILENAME = 'C:\\exercises\\jp.ldf');",
                              connection);

            connection.Open();
            command.ExecuteNonQuery();
        }

        using (SqlConnection connection =
            new SqlConnection("Data Source=(local);" +
                              "Database='JacksonPetroleum';" +
                              "Integrated Security=yes;"))
        {
            SqlCommand command =
                new SqlCommand("CREATE TABLE Seasonals(" +
                               "ContractorCode nchar(10)," +
                               "FirstName nvarchar(20)," +
                               "LastName nvarchar(20)," +
                               "Wage money);",
                               connection);

            connection.Open();
            command.ExecuteNonQuery();
        }

        using (SqlConnection connection =
            new SqlConnection("Data Source=(local);" +
                              "Database='JacksonPetroleum';" +
                              "Integrated Security=yes;"))
        {
            SqlCommand command =
                new SqlCommand("INSERT INTO Seasonals " +
                               "VALUES(N'86824', N'Julie', N'Chance', 12.84)," +
                               "      (N'84005', N'Ayinda', N'Kaihibu', 9.52);",
                               connection);
            connection.Open();
            command.ExecuteNonQuery();
        }

        using (SqlConnection connection =
            new SqlConnection("Data Source=(local);" +
                              "Database='JacksonPetroleum';" +
                              "Integrated Security=yes;"))
        {
            SqlCommand command =
                new SqlCommand("CREATE TABLE Employees(" +
                               "EmplNbr nchar(10)," +
                               "FirstName nvarchar(20)," +
                               "LastName nvarchar(20)," +
                               "Salary money);",
                               connection);

            connection.Open();
            command.ExecuteNonQuery();
        }

        using (SqlConnection connection =
            new SqlConnection("Data Source=(local);" +
                              "Database='JacksonPetroleum';" +
                              "Integrated Security=yes;"))
        {
            SqlCommand command =
                new SqlCommand("INSERT INTO Employees " +
                               "VALUES(N'22684', N'Ann', N'Keans', 20.52), " +
                               "      (N'48157', N'Godwin', N'Harrison', 18.75), " +
                               "      (N'82476', N'Timothy', N'Journ', 28.04), " +
                               "      (N'15007', N'Ralph', N'Sunny', 22.64);",
                               connection);
            connection.Open();
            command.ExecuteNonQuery();
            MessageBox.Show("A database named JacksonPetroleum has been " +
                            "created, along with its tables.",
                            "Jackson Petroleum",
                            MessageBoxButtons.OK,
                            MessageBoxIcon.Information);
        }
    }

    void btnDatabaseClick(object sender, EventArgs e)
    {
        CreateDatabase();
    }

    public static int Main()
    {
        System.Windows.Forms.Application.Run(new Exercise());
        return 0;
    }
}

(The file could be compiled from the Command Prompt as follows:

C:\Exercise1>C:\Windows\Microsoft.NET\Framework\v4.0.30319\csc /r:System.Windows
.Forms.dll /t:winexe Exercise.cs
Microsoft (R) Visual C# Compiler version 4.0.30319.17929
for Microsoft (R) .NET Framework 4.5
Copyright (C) Microsoft Corporation. All rights reserved.

The file could be executed from the Command Prompt as follows:

C:\Exercise1>Exercise

)

As a result, the Seasonals table contains the following records:

using System;
using System.Drawing;
using System.Data;
using System.Windows.Forms;
using System.Data.SqlClient;

public class Exercise : System.Windows.Forms.Form
{
    Button btnDatabase;
    DataGridView dgvSeasonals;

    public Exercise()
    {
        InitializeComponent();
    }

    void InitializeComponent()
    {
        btnDatabase = new Button();
        btnDatabase.Text = "Database";
        btnDatabase.Location = new Point(12, 12);
        btnDatabase.Click += new EventHandler(btnDatabaseClick);

        dgvSeasonals = new DataGridView();
        dgvSeasonals.Location = new Point(12, 44);
        dgvSeasonals.Size = new System.Drawing.Size(470, 270);
        dgvSeasonals.Anchor = AnchorStyles.Left | AnchorStyles.Top |
                              AnchorStyles.Right | AnchorStyles.Bottom;

        Text = "Jackson Petroleum";
        Size = new System.Drawing.Size(500, 350);
        Controls.Add(btnDatabase);
        Controls.Add(dgvSeasonals);
    }

    void ShowRecords()
    {
        using (SqlConnection connection =
                    new SqlConnection("Data Source=(local);" +
                                      "Database='JacksonPetroleum';" +
                                      "Integrated Security=yes;"))
        {
            SqlCommand command = new SqlCommand("SELECT * FROM Seasonals;",
                    connection);

            connection.Open();
            command.ExecuteNonQuery();

            SqlDataAdapter sdaSeasonals = new SqlDataAdapter(command);
            BindingSource bsSeasonals = new BindingSource();

            DataSet dsSeasonals = new DataSet("SeasonalsSet");
            sdaSeasonals.Fill(dsSeasonals);

            bsSeasonals.DataSource = dsSeasonals.Tables[0];
            dgvSeasonals.DataSource = bsSeasonals;
        }
    }

    void btnDatabaseClick(object sender, EventArgs e)
    {
        ShowRecords();
    }


    public static int Main()
    {
        System.Windows.Forms.Application.Run(new Exercise());
        return 0;
    }
}

Seasonals

The Employees table contains the following records:

Employees

Copying All Records From a Table

Copying the records consists of transferring them from one table, the source, to another table, the target. You can copy all records from the source to the target. You can select what columns to copy. Or you can specify under what conditions some records would be copied.

To copy all records from one table to another, the source and the target must have the same number of columns and the same sequence of columns with regards to their data types. To copy the records, Start an INSERT or INSERT INTO statement. Instead of the VALUES keyword, create a SELECT statement that involves the source table. Here is an example:

void CopyRecords()
{
    using (SqlConnection connection =
                new SqlConnection("Data Source=(local);" +
                                  "Database='JacksonPetroleum';" +
                                  "Integrated Security=yes;"))
    {
        SqlCommand command = new SqlCommand("INSERT INTO Employees " +
                                            "SELECT * FROM Seasonals;",
                    			    connection);

        connection.Open();
        command.ExecuteNonQuery();
    }
}

Once this statement has been executed, all records from the source table are copied to the target table:

Employees

If you use the above formula, the records of a column from the source table would be copied to the corresponding column of the target table. Sometimes, you will want to merge tables that neither share the same sequence of columns nor have the same number of columns. Consider the following tables:

using System;
using System.Drawing;
using System.Data;
using System.Windows.Forms;
using System.Data.SqlClient;

public class Exercise : System.Windows.Forms.Form
{
    Button btnTables;
    Button btnCopyRecords;
    DataGridView dgvEmployees;
    Button btnSelectEmployees;

    public Exercise()
    {
        InitializeComponent();
    }

    void InitializeComponent()
    {
        btnTables = new Button();
        btnTables.AutoSize = true;
        btnTables.Text = "Create Tables";
        btnTables.Location = new Point(12, 12);
        btnTables.Click += new EventHandler(btnTablesClick);

        dgvEmployees = new DataGridView();
        dgvEmployees.Width = 360;
        dgvEmployees.Location = new Point(12, 44);

        btnCopyRecords = new Button();
        btnCopyRecords.AutoSize = true;
        btnCopyRecords.Location = new Point(110, 12);
        btnCopyRecords.Text = "Copy Records";
        btnCopyRecords.Click += new EventHandler(btnCopyRecordsClick);

        btnSelectEmployees = new Button();
        btnSelectEmployees.Text = "Show Employees";
        btnSelectEmployees.AutoSize = true;
        btnSelectEmployees.Location = new Point(210, 12);
        btnSelectEmployees.Click += new EventHandler(btnSelectEmployeesClick);

        Controls.Add(btnTables);
        Size = new Size(400, 245);
        Controls.Add(dgvEmployees);
        Controls.Add(btnCopyRecords);
        Text = "Copying From a Table";
        Controls.Add(btnSelectEmployees);

        dgvEmployees.Anchor = AnchorStyles.Left | AnchorStyles.Top |
                              AnchorStyles.Right | AnchorStyles.Bottom;
    }

    void btnTablesClick(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("CREATE TABLE Seasonals(" +
                                         "ContractorCode nchar(10), Wage money, " +
                                         "LastName nvarchar(20), FirstName nvarchar(20));" +
                                         "CREATE TABLE Employees(EmplNbr nchar(10), " +
                                         "EmployeeName nvarchar(50), HourlySalary money);",
                                         cntExercise);


            cntExercise.Open();
            cmdExercise.ExecuteNonQuery();
        }

        using (cntExercise =
            new SqlConnection("Data Source=(local);" +
                              "Database='Exercise1';" +
                              "Integrated Security=yes;"))
        {
            cmdExercise = new SqlCommand("INSERT INTO Seasonals VALUES" +
                                         "(N'86824', 12.84, N'Chance', N'Julie')," +
                                         "(N'84005', 9.52, N'Kaihibu', N'Ayinda');" +
                                         "INSERT INTO Employees VALUES" +
                                         "(N'22684', N'Ann Keans', 20.52)," +
                                         "(N'48157', N'Godwin Harrison', 18.75)," +
                                         "(N'82476', N'Timothy Journ', 18.75)," +
                                         "(N'15007', N'Ralph Sunny', 18.75);",
                                         cntExercise);
            cntExercise.Open();
            cmdExercise.ExecuteNonQuery();
            MessageBox.Show("Two tables named Employees and Seasonals have been " +
                            "created and records were added to them.",
                            "Copying From a Table",
                            MessageBoxButtons.OK, MessageBoxIcon.Information);
        }
    }

    void btnCopyRecordsClick(object sender, EventArgs e)
    {
        // . . .
    }

    void btnSelectEmployeesClick(object sender, EventArgs e)
    {
        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("ContractorsSet");

            sdaExercise.Fill(dsExercise);
            dgvEmployees.DataSource = dsExercise.Tables[0];
        }
    }

    public static int Main()
    {
        System.Windows.Forms.Application.Run(new Exercise());
        return 0;
    }
}

Employees

Before copying the records, you must analyze the table to figure out a way to converge the records. Here is an example:

void btnCopyRecordsClick(object sender, EventArgs e)
{
    using (SqlConnection cntExercise =
            new SqlConnection("Data Source=(local);" +
                              "Database='Exercise1';" +
                              "Integrated Security=yes;"))
    {
        SqlCommand cmdExercise = 
            new SqlCommand("INSERT INTO Employees " +
                           "SELECT ContractorCode, FirstName + N' ' + Lastname, Wage FROM Seasonals;",
                               cntExercise);

        cntExercise.Open();
        cmdExercise.ExecuteNonQuery();
        MessageBox.Show("The seasonal contractors have been converted to employees.",
                        "Copying From a Table",
                        MessageBoxButtons.OK, MessageBoxIcon.Information);
    }
}

In the same way, you can set a condition to follow when copying the records.

Copying From Many Tables

You can use the ability to copy records to get records from two or more tables and add them to a another table.

The formula to follow is:

INSERT INTO TableName
SELECT WhatField(s) FROM OneTable
UNION [ALL]
SELECT WhatField(s) FROM AnotherTable;

Copying a Set of Records

Imagine you have an existing table filled with records. Instead of copying all records from that table into another table, you may want to copy only a specific number of records. To do this, use the following formula:

INSERT TOP (Number) [INTO] TargetTable
SELECT WhatObject(s) FROM WhatObject

After the INSERT keyword, add TOP followed by parentheses. In the parentheses, enter the desired number of records. The rest of the formula follows the techniques we hase used so far. Here is an example:

USE Exercise;
GO

CREATE TABLE Interns
(
	InternNumber nchar(10),
	LastName nvarchar(20),
	FirstName nvarchar(20),
	Salary money
);
GO

INSERT INTO Interns
VALUES(N'30848', N'Politanoff', N'Jeannette', 22.04),
      (N'81094', N'Bragg', N'Salomon', 15.50),
      (N'20938', N'Verne', N'Daniel', 21.24),
      (N'11055', N'Beal', N'Sidonie', 12.85),
      (N'88813', N'Jensen', N'Nicholas', 20.46);
GO

INSERT TOP (3) INTO Employees
SELECT InternNumber, FirstName + ' ' + Lastname, Salary FROM Interns;
GO

SELECT * FROM Employees;
GO

In this example, only 3 records from the source table were copied.

Copying a Percentage of Records

Instead of copying a fixed number of records, you can specify a portion as a percentage. In this case, use the following formula:

INSERT TOP (Number) PERCENT [INTO] TargetTable
SELECT WhatObject(s) FROM WhatObject

The new keyword in this formula is PERCENT. You must use it to indicate that the number in the parentheses represents a percentage value. That number must be between 0.00 and 100.00 included. Here is an example:

USE Exercise;
GO
INSERT INTO Interns
VALUES(N'28440', N'Avery', N'Herbert', 13.74),
      (N'60040', N'Lynnette', N'Douglas', 17.75),
      (N'25558', N'Washington', N'Jacob', 20.15),
      (N'97531', N'Colson', N'Lois', 17.05),
      (N'24680', N'Meister', N'Victoria', 11.60);
GO
INSERT TOP (30) PERCENT INTO Employees
SELECT InternNumber, FirstName + N' ' + Lastname, Salary FROM Interns;
GO

In this case, the source table (the Interns table) has nine records but only three (9 / (100/30) = 9 / 3.33 = 2.7 � 3 (the closest higher integer to 2.7 is 3)) record from that table were copied into the Employees table.