/* 
 * Copies a table from one database to the other.
 *  
 * Use the program like this:
 * 
 *    MSSQLCopy <connectionstring-SOURCE> <tablename> <connectionstring-TARGET>
 * 
 * This program expects the tables to be empty before you insert. If they aren't you are going to receive errors.
 * There is no error handling whatsoever and you'll have to look at the exceptions and be clever.
 * 
 */


using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.Sql;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace MSSQLCopy
{
    class Program
    {
        static void Main(string[] args)
        {
            //You must use the $ after the object you reference in the spreadsheet
            SqlConnection target = new SqlConnection(args[2]);
            target.Open();

            //You must use the $ after the object you reference in the spreadsheet
            using (SqlConnection conn = new SqlConnection(args[0]))
            {
               conn.Open();               
               using (SqlCommand cmd = new SqlCommand("SELECT * FROM [" + args[1] + "]", conn))
                {
                    using (SqlDataReader rdr = cmd.ExecuteReader())
                    {
                        using (SqlCommand cmdA = new SqlCommand("SET IDENTITY_INSERT " + args[1] + " ON", target))
                        {
                            try
                            {
                                cmdA.ExecuteScalar();
                            }
                            catch (Exception)
                            {

                            }
                        }

                        while (rdr.Read())
                        {
                            StringBuilder fields = new StringBuilder();
                            StringBuilder data = new StringBuilder();
                            using (SqlCommand insert = new SqlCommand("", target))
                            {
                                for (int i = 0; i < rdr.FieldCount; i++)
                                {
                                    if (i > 0)
                                    {
                                        fields.Append(", ");
                                        data.Append(", ");
                                    }
                                    fields.Append(rdr.GetName(i));
                                    data.Append("@" + rdr.GetName(i));
                                    insert.Parameters.AddWithValue("@" + rdr.GetName(i), rdr.GetValue(i));
                                }
                                insert.CommandText = "INSERT INTO " + args[1] + " (" + fields + ") VALUES (" + data + ")";
                                insert.ExecuteNonQuery();
                                Console.Write(insert.CommandText);
                            }
                        }
                        using (SqlCommand cmdAun = new SqlCommand("SET IDENTITY_INSERT " + args[1] + " OFF", target))
                        {
                            try
                            {
                                cmdAun.ExecuteScalar();
                            }
                            catch (Exception)
                            {

                            }
                        }
                    }
                }
            }
        }
    }
}