Combining SQL Tables into One Table in New DB with Similar Fields

Question

I have a SQL Server Database filled with Reports all having the same schema.

What I've done is created a .NET application to automate the process of adding all reports to a new table.

How can the code be modified to increase performance? Is there a better approach then using: conn.Close(); dr.Close(); conn.Open(); successively to insert the tables?

        static void Main(string[] args)
    {
        //Base Report Must Have some form of "Base" in Table Name
        string repColsString = "[";
        // Connection strings exist in working code          
        string sqlConnMove = @"";
        string sqlConnBaseline = @"";

        Console.Write("What database has the reports? ");
        string mainDB = Console.ReadLine();
        List<string> connStringSplit = sqlConnMove.Split(';').ToList();
        connStringSplit[1] = "Initial Catalog=" + mainDB;
        string connString = string.Join(";", connStringSplit);
        Console.Write("Enter Shared Name Across All Reports: ");
        string commonName = Console.ReadLine();

        SqlConnection conn = new SqlConnection(connString);
        conn.Open();

        //BUILD DB
        Console.Write("Enter the New Database Name: ");
        string DBname = Console.ReadLine();
        using (SqlConnection conn2 = new SqlConnection(sqlConnMove))
        {
            conn2.Open();
            var createDB = conn2.CreateCommand();
            createDB.CommandText = "CREATE DATABASE " + DBname;
            createDB.ExecuteNonQuery();
            Console.WriteLine("DB " + DBname + " created.");
        }

        List<string> tables = new List<string>();
        DataTable t = conn.GetSchema("Tables");

        Console.Write("Enter Destination Combined Table Name: ");
        string destinationTableName = Console.ReadLine();
        string baseReport = "";

        foreach (DataRow row in t.Rows)
        {
            string tablename = (string)row[2];
            if (tablename.Contains(commonName)) { tables.Add(tablename); }
            if (tablename.ToLower().Contains("base")) { baseReport = tablename; };
            if (tables.Contains(baseReport)) { tables.Remove(baseReport); }
        }

        //ADD BASE REPORT TO NEW DB
        String insertBaseToDb = String.Format("SELECT * INTO [{0}].[dbo].[{1}] " +
            "FROM [XMfb].[dbo].[{2}];", DBname ,destinationTableName, baseReport);
        using (SqlCommand addBaseReport = new SqlCommand(insertBaseToDb, conn))
        {
            addBaseReport.ExecuteNonQuery();
            Console.WriteLine("Base Report: " + baseReport + " added." );
        }

        foreach (var insertTable in tables)
        {
            string colQuery = String.Format("SELECT * FROM [XMfb].[dbo].[{0}]", insertTable );
            using (conn = new SqlConnection(sqlConnBaseline))
            {

                conn.Open();
                //Console.WriteLine("State" + conn.State);
                //Console.WriteLine("Timeout" + conn.ConnectionTimeout);

                using (SqlCommand cmd = new SqlCommand(colQuery, conn))
                {
                    try
                    {
                        SqlDataReader dr = cmd.ExecuteReader();
                        cmd.CommandTimeout = 45;
                        while (dr.Read())
                        {
                            var columns = new List<string>();

                            for (int i = 0; i < dr.FieldCount; i++)
                            {
                                string colName = dr.GetName(i);
                                columns.Add(dr.GetName(i));
                                repColsString += colName;
                            }

                            repColsString = String.Join("],[", columns);
                            repColsString = "[" + repColsString + "]";

                            // Modify this?
                            conn.Close();
                            dr.Close();
                            conn.Open();
                            // THIS WORRIES ME

                            using (SqlCommand insCom = conn.CreateCommand())
                            {
                                insCom.CommandType = CommandType.Text;
                                string insertQuery = "INSERT INTO [" + DBname + " ].[dbo].["+ destinationTableName + "] (" + repColsString + ") " +
                                    "SELECT " + repColsString + " FROM [XMfb].[dbo].[" + insertTable + "];";

                                //Console.WriteLine(insertQuery);
                                insCom.CommandText = insertQuery;
                                //insCom.Parameters.Add(new SqlParameter("@COLUMNS", "works"));

                                insCom.ExecuteNonQuery();
                                Console.WriteLine("\n" + insertTable + " added. ");
                            }                                
                            break;
                        }
                    }
                    catch (Exception ex)
                    {
                        StringBuilder sb = new StringBuilder();
                        Console.WriteLine(sb.Append(ex.Message) + " on table " + insertTable + ".\n");
                    }
                }
            }
        }

Answer 1

If you use 3 part names [db].[owner].[table] you should be able to use one connection and one command.

Should have one try and put .Open() in the try.

Put connection, command, and reader in using blocks.

What is the purpose of this?

StringBuilder sb = new StringBuilder();
Console.WriteLine(sb.Append(ex.Message) + " on table " + insertTable + ".\n");

Why create a StringBuilder for one text?

Inconsistent use of String.Format versus + .

Something like this:

static void ReuseConn(string[] args)
{
    string repColsString = "[";
    string sqlConnBase = @"con;base;string";
    string sqlConnMove = @"con;move;string";

    Console.Write("What database has the reports? ");
    string mainDB = Console.ReadLine();
    List<string> connStringSplit = sqlConnMove.Split(';').ToList();
    connStringSplit[1] = $"Initial Catalog={mainDB}";
    string connString = string.Join(";", connStringSplit);
    Console.Write("Enter Shared Name Across All Reports: ");
    string commonName = Console.ReadLine();
    SqlConnection conn = new SqlConnection(connString);
    string insertTableEx = string.Empty;
    try
    {
        conn.Open();
        Console.Write("Enter the New Database Name: ");
        string DBname = Console.ReadLine();
        using (SqlCommand cmd = conn.CreateCommand())
        {
            cmd.CommandText =
            cmd.CommandText = "CREATE DATABASE " + DBname;
            cmd.ExecuteNonQuery();
            Console.WriteLine("DB " + DBname + " created.");

            List<string> tables = new List<string>();
            DataTable t = conn.GetSchema("Tables");

            Console.Write("Enter Destination Combined Table Name: ");
            string destinationTableName = Console.ReadLine();
            string baseReport = "";

            foreach (DataRow row in t.Rows)
            {
                string tablename = (string)row[2];
                if (tablename.Contains(commonName)) { tables.Add(tablename); }
                if (tablename.ToLower().Contains("base")) { baseReport = tablename; };
                if (tables.Contains(baseReport)) { tables.Remove(baseReport); }
            }

            //ADD BASE REPORT TO NEW DB
            String insertBaseToDb = $"SELECT * INTO [{DBname}].[dbo].[{destinationTableName}] FROM [XMfb].[dbo].[{baseReport}]";
            cmd.CommandText = insertBaseToDb;
            cmd.ExecuteNonQuery();
            Console.WriteLine("Base Report: " + baseReport + " added.");

            foreach (var insertTable in tables)
            {
                insertTableEx = insertTable;
                string colQuery = $"SELECT * FROM [XMfb].[dbo].[{insertTable}]";
                cmd.CommandText = colQuery;
                cmd.CommandTimeout = 45;
                using (SqlCommand insCom = conn.CreateCommand())  //need to enable MARS
                using (SqlDataReader dr = cmd.ExecuteReader())
                {                          
                    while (dr.Read())
                    {
                        var columns = new List<string>();

                        for (int i = 0; i < dr.FieldCount; i++)
                        {
                            string colName = dr.GetName(i);
                            columns.Add(dr.GetName(i));
                            repColsString += colName;
                        }

                        repColsString = String.Join("],[", columns);
                        repColsString = "[" + repColsString + "]";

                        //here you do need a new command but you should not be creating it in the loop
                        insCom.CommandType = CommandType.Text;
                        string insertQuery = $"INSERT INTO [{DBname}].[dbo].[{destinationTableName}] ({repColsString})" + Environment.NewLine +
                                                $"SELECT {repColsString} FROM [XMfb].[dbo].[{insertTable}];";

                        //Console.WriteLine(insertQuery);
                        insCom.CommandText = insertQuery;

                        insCom.ExecuteNonQuery();

                        Console.WriteLine($"\n{insertTable} added.");
                        //break;  // you are only processing the first row I hope this is a mistake 
                    }
                }
            }
        }
    }
    catch (Exception ex)
    {
        Console.WriteLine($"{ex.Message} on table {insertTableEx}");
    }
    finally
    {
        conn.Close();
    }
}

Answer 2

Parallel programming will be my answer to cases like this. In your cases this will help a lot when processing a lot of tables, because it will parallelize your code to work across multiple core CPU.

You could easily change your code using Parallel.Foreach like bellow:

 Parallel.ForEach<DataRow> (t.Rows, (row) => 
 {
     string tablename = (string)row[2];
     if (tablename.Contains(commonName)) { tables.Add(tablename); }
     if (tablename.ToLower().Contains("base")) { baseReport = tablename; };
     if (tables.Contains(baseReport)) { tables.Remove(baseReport); }
 });

This can also be parallelize.

Parallel.ForEach<string> (tables, (insertTable) =>
{
});

Do note that Parallel.Foreach doesn't guarantee order execution, so If you need to process it sequential than you need another approach.

https://msdn.microsoft.com/en-us/library/dd992001%28v=vs.110%29.aspx?f=255&MSPPError=-2147217396

And since you are open up new connection under for each, you could safely use this command. Each thread will run each own connection, this way no shared sqlconnection/sqlcommand instance will be use together.