What is the best way to get rows of data and its datatypes?

Question

Currently, I’ve been working to translate a whole table and put it into another table with same schema.

Given: Since the table data rows are more than a thousand rows, it is quite hard to translate all of that in one transaction

I also need to know its datatypes since not all of the columns are translatable.

Plan: My initial plan is to get the users by batch(e.g. top 10 first) and put it into a “datatable”. Reason is because datatable has a column list which holds the columns datatype. This plan I think is JUST OK.

Drawback: Putting it into a datatable, I know, would be slow. I wouldnt be able to hide it even if batch it. Just a little bit mitigate it.

On the otherhand, if I put the data into a list, instead of datatable, transaction would be faster. But this will result to another sqlcommand call to get the data type schema of the table.

Question: Is there a way I could the best of both worlds? Faster and a one call, data value and datatype together. Note, In this case, aside from the row data, I just need the data type of the column.

Answer 1

One technique might be to use BulkCopy. Simply read the schema off the first table. Create the target table, define column mappings and do the bulk copy. I have seen this rip through hundreds of thousands of records in seconds.

string connectionString = GetConnectionString();
// Open a sourceConnection to the AdventureWorks database.
using (SqlConnection sourceConnection =
    new SqlConnection(connectionString))
{
    sourceConnection.Open();

    // Perform initial schema read and create target table

    // Get data from the source table as a SqlDataReader.
    SqlCommand commandSourceData = new SqlCommand(
        "SELECT ProductID, Name, " +
        "ProductNumber " +
        "FROM Production.Product;", sourceConnection);
    SqlDataReader reader =
        commandSourceData.ExecuteReader();

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

        // Set up the bulk copy object.
        using (SqlBulkCopy bulkCopy =
            new SqlBulkCopy(destinationConnection))
        {
            bulkCopy.DestinationTableName =
                "dbo.BulkCopyDemoMatchingColumns";

            bulkCopy.ColumnMappings.Add("SourceColumn1", "TargetColumn1");
            bulkCopy.ColumnMappings.Add("SourceColumn2", "TargetColumn2");

            try
            {
                // Write from the source to the destination.
                bulkCopy.WriteToServer(reader);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                // Close the SqlDataReader. The SqlBulkCopy
                // object is automatically closed at the end
                // of the using block.
                reader.Close();
            }
        }
    }
}

https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql/multiple-bulk-copy-operations