using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public class ExecAndInsert {

   static string quotename(string str) {
      return string.Concat("[", str.Replace("]", "]]"), "]");
   }

   static void _ExecAndInsert(string  server,
                              string  dbname,
                              string  schema,
                              string  table,
                              string  srcConnString,
                              string  SqlCmdText) {


      // Set up connection string for target.
      SqlConnectionStringBuilder trgConnString =
                     new SqlConnectionStringBuilder();
      trgConnString.DataSource = server;
      trgConnString.InitialCatalog = dbname;
      trgConnString.IntegratedSecurity = true;

      // Open the connections, to have that business don.
      using (SqlConnection srccn = new SqlConnection(srcConnString)) {
      using (SqlConnection trgcn = new SqlConnection(trgConnString.ConnectionString)) {
         srccn.Open();
         trgcn.Open();

         // Set up commands for source and target.
         using (SqlCommand srccmd = new SqlCommand(SqlCmdText, srccn)) {
         using (SqlCommand trgcmd = new SqlCommand()) {
            srccmd.CommandType = CommandType.Text;
            trgcmd.CommandType = CommandType.Text;
            trgcmd.Connection = trgcn;

            // Execute the command.
            using (SqlDataReader rdr = srccmd.ExecuteReader()) {

               int resultno = 0;
               // Now process all result sets.
               do {
                  string column_list = "";
                  resultno++;
                  string table_name = quotename(schema) + '.' +
                                      quotename(table + (resultno == 1 ? "" : "_" + resultno.ToString()));
                  System.Collections.Hashtable colnames = new System.Collections.Hashtable();

                  // Get a schema table for the result set.
                  DataTable schemaTable = rdr.GetSchemaTable();

                  // Iterate over all the columns.
                  foreach (DataRow coldesc in schemaTable.Rows) {
                     // Get essential column information.
                     int    colno     = (int)    coldesc["ColumnOrdinal"];
                     string colname   = (string) coldesc["ColumnName"];
                     string datatype  = (string) coldesc["DataTypeName"];
                     int    maxlength = (int)    coldesc["ColumnSize"];
                     short  precision = (short)  coldesc["NumericPrecision"];
                     short  scale     = (short)  coldesc["NumericScale"];

                     // Terminate previous column with comma new line
                     if (colno > 0) {
                        column_list += ",\n";
                     }

                     // Increment column number.
                     colno++;

                     // If there is no column name, apply a default.
                     if (colname == null|| colname == "") {
                        colname = "Col " + colno.ToString();
                     }

                     // Handle colname duplicates.
                     while (colnames.Contains(colname)) {
                        colname += " A";
                     }
                     colnames.Add(colname, 1);

                     // Add the column name to the column list.
                     column_list += quotename(colname) + " ";

                     // Add the data type.
                     switch (datatype) {
                        case "bigint"            :
                        case "bit"               :
                        case "date"              :
                        case "datetime"          :
                        case "float"             :
                        case "geography"         :
                        case "geometry"          :
                        case "hierarchyid"       :
                        case "image"             :
                        case "int"               :
                        case "money"             :
                        case "ntext"             :
                        case "real"              :
                        case "smalldatetime"     :
                        case "smallint"          :
                        case "smallmoney"        :
                        case "sql_variant"       :
                        case "sysname"           :
                        case "text"              :
                        case "tinyint"           :
                        case "uniqueidentifier"  :
                        case "xml"               : column_list += datatype;
                                                   break;
                        case "timestamp"         :
                        case "rowversion"        : throw new Exception("Unsupported typename: " + datatype);

                        case "binary"            :
                        case "varbinary"         :
                        case "char"              :
                        case "varchar"           :
                        case "nchar"             :
                        case "nvarchar"          :  column_list += datatype + "(" +
                                                       (maxlength >= 1 && maxlength <= 8000 ?
                                                           maxlength.ToString() : "MAX") + ")";
                                                    break;
                        case "decimal"           :
                        case "numeric"           :  column_list += datatype + "(" +
                                                        precision.ToString() + "," + scale.ToString() + ")";
                                                    break;
                        case "time"              :
                        case "datetime2"         :
                        case "datetimeoffset"    :   column_list += datatype + "(" +
                                                        scale.ToString() + ")";
                                                    break;
                        default :
                           // If we come here, we presumably have a CLR type, as they come on the form
                           // database.schema.name. There is no bracketing, so if we have more than two
                           // periods, we don't know where we belong.

                           // In practice we first check for the three native CLR types.
                           if (datatype.EndsWith(".sys.geography")) {
                              column_list += "geography";
                           }
                           else if (datatype.EndsWith(".sys.geometry")) {
                              column_list += "geometry";
                           }
                           else if (datatype.EndsWith(".sys.hierarchyid")) {
                              column_list += "hierarchyid";
                           }
                           else if (datatype.StartsWith(dbname + ".")) {
                              // Seems to be a type in the current database. Strip of the database part.
                              datatype = datatype.Substring(dbname.Length + 1);

                              // split into parts.
                              string[] Parts = datatype.Split('.');

                              // If more than two parts it's game over - we can't tell where the dot belongs to.
                              if (Parts.Length > 2) {
                                  throw new Exception("Unsupported typename: " + datatype);
                              }

                              column_list += quotename(Parts[0]) + "." + quotename(Parts[1]);
                           }
                           else { // Bad news.
                              throw new Exception("Unsupported typename: " + datatype);
                           }
                           break;
                     }
                  }

                  // Create table and table type.
                  trgcmd.CommandText = "CREATE TABLE " + table_name + "(" + column_list + ")";
                  trgcmd.ExecuteNonQuery();
                  trgcmd.CommandText = "CREATE TYPE " + table_name + " AS TABLE (" + column_list + ")";
                  trgcmd.ExecuteNonQuery();

                  // Add pass the reader to the tvp to transfer all the data.
                  trgcmd.CommandText = "INSERT " + table_name + " SELECT * FROM @tvp";
                  trgcmd.Parameters.Add("@tvp", SqlDbType.Structured).Value = rdr;
                  trgcmd.Parameters["@tvp"].Direction = ParameterDirection.Input;
                  trgcmd.Parameters["@tvp"].TypeName = table_name;
                  trgcmd.ExecuteNonQuery();

                  trgcmd.Parameters.Clear();

                  // Drop the table type.
                  trgcmd.CommandText = "DROP TYPE " + table_name;
                  trgcmd.ExecuteNonQuery();

               } while (rdr.NextResult());
            }
         }}
      }}
   }

   [Microsoft.SqlServer.Server.SqlProcedure]
   public static void CLREntry(String  server,
                               String  dbname,
                               String  schema,
                               String  table,
                               String  SqlCmdText) {
      _ExecAndInsert(server, dbname, schema, table,
                    @"context connection=true", SqlCmdText);
   }


   static void Main(string[] args) {
      SqlConnectionStringBuilder srcConnString =
                     new SqlConnectionStringBuilder();
      srcConnString.DataSource = args[0];
      srcConnString.InitialCatalog = args[1];
      srcConnString.IntegratedSecurity = true;
      _ExecAndInsert(args[0], args[1], args[2], args[3],
                     srcConnString.ConnectionString, args[4]);
   }
}