Is there a simpler way to write a row from one table to another?

Question

I have the following code:

const String sqlSelect = "SELECT * FROM UserPasswords WHERE username='System Administrator';";
const String sqlInsert = "INSERT INTO UserPasswords VALUES (@username,@Password,@startDate,@Expired,@UserPasswordsID)";
using (var con1 = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["dataConnectionString"].ConnectionString))
using (var con2 = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["utilConnectionString"].ConnectionString))
{
  con1.Open();
  con2.Open();
  using (var selectCommand = new SqlCommand(sqlSelect, con1))
  {
    using (var reader = selectCommand.ExecuteReader())
    {
      if (reader.Read())
      {
        using (var insertCommand = new SqlCommand(sqlInsert, con2))
        {
          insertCommand.Parameters.Add(new SqlParameter("@username", reader.GetString(0)));
          insertCommand.Parameters.Add(new SqlParameter("@Password", reader.GetString(1)));
          insertCommand.Parameters.Add(new SqlParameter("@startDate", reader.GetDateTime(2)));
          insertCommand.Parameters.Add(new SqlParameter("@Expired", reader.GetBoolean(3)));
          insertCommand.Parameters.Add(new SqlParameter("@UserPasswordsID", reader.GetInt32(4)));
          insertCommand.ExecuteNonQuery();
        }
      }
    }
  }

All I need to do is copy the row with the 'System Administrator' info into another table as a backup. Though this code works is there someway to do this more elegantly?

Answer 1

Transferring Data Using SqlBulkCopy

private static void PerformBulkCopy()
{
    string connectionString =
            @"Server=localhost;Database=Northwind;Trusted_Connection=true";
    // get the source data
    using (SqlConnection sourceConnection = 
            new SqlConnection(connectionString))
    {
        SqlCommand myCommand =
            new SqlCommand("SELECT * FROM Products_Archive", sourceConnection);
        sourceConnection.Open();
        SqlDataReader reader = myCommand.ExecuteReader();

        // open the destination data
        using (SqlConnection destinationConnection =
                    new SqlConnection(connectionString))
        {
            // open the connection
            destinationConnection.Open();

            using (SqlBulkCopy bulkCopy =
            new SqlBulkCopy(destinationConnection.ConnectionString))
            {
                bulkCopy.BatchSize = 500;
                bulkCopy.NotifyAfter = 1000;
                bulkCopy.SqlRowsCopied +=
                    new SqlRowsCopiedEventHandler(bulkCopy_SqlRowsCopied);
                bulkCopy.DestinationTableName = "Products_Latest";
                bulkCopy.WriteToServer(reader);
            }
        }
        reader.Close();
    }
}

Solution #2: stored procedure

Answer 2

Why not just INSERT INTO newtable (SELECT * FROM UserPasswords WHERE username='System Administrator');?