locked
SqlBulkCopy RRS feed

  • Question

  • When using SqlBulkCopy, can you retrieve the timestamp of the inserted/updated records?
    Anonymous
    Friday, August 19, 2011 6:06 PM

Answers

  • I have a timestamp field in the db. My question is how do i retrieve the timestamp value when using SqlBulkCopy?


     

    Hi phbuser

     

    The SqlBulkCopy Class (System.Data.SqlClient) is used to load/copy data from a table to another similar table in batch. For example as below.

    During the BulkCopy operation, you can include TimeStamp filed.

    If you want to retrieve the TimeStamp value, you can iterate the SqlDataReader object.

                    while (reader.Read())

                    {

                        DateTime ts = reader.GetDateTime(3); //Assume that the TimeStamp is the 4th filed

                    }

     

    http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy(v=vs.85).aspx#Y769

     

                string connectionString = GetConnectionString();

                // Open a sourceConnection to the AdventureWorks database.

                using (SqlConnection sourceConnection = new SqlConnection(connectionString))

                {

                    sourceConnection.Open();

                    // Get data from the source table as a SqlDataReader.

                    SqlCommand commandSourceData = new SqlCommand("SELECT ProductID, Name, ProductNumber, TimeStamp FROM Production.Product;", sourceConnection);

                    SqlDataReader reader = commandSourceData.ExecuteReader();

     

                    // Open the destination connection

                    // Use SqlBulkCopy to move data from one table to the other table

                    using (SqlConnection destinationConnection = new SqlConnection(connectionString))

                    {

                        destinationConnection.Open();

                        // Set up the bulk copy object.

                        // Note that the column positions in the source

                        // data reader match the column positions in

                        // the destination table so there is no need to

                        // map columns.

                        using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnection))

                        {

                            bulkCopy.DestinationTableName = "dbo.BulkCopyDemoMatchingColumns";

                            try

                            {

                                // Write from the source to the destination.

                                bulkCopy.WriteToServer(reader);

                            }

                            catch (Exception ex)

                            {

                                Console.WriteLine(ex.Message);

                            }

                            finally

                            {

                                // Close the SqlDataReader. The SqlBulkCopy

                                reader.Close();

                            }

                        }

     

                    }

                }

     

    By the way, SQL Server Data Access Forum is more appropriate for such questions. Maybe a moderator will move it there.

    http://social.msdn.microsoft.com/Forums/en-US/sqldataaccess/threads


    Martin Xie [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, August 23, 2011 9:22 AM

All replies

  • I assume you are taking about the BCP / Bulk insert as this is a SQL forum....

    If the context of your question is .NET Framework.... then this is not the right place to post your question, please post in the .NET related forums....

     

    Incase of SQL Server we use BCP /BULK Insert to do the bulk load into the data, but I have not heard of any such facility for updates yet.....

    if you need to insert the timestamp of the inserted record in your table, you can use the default constraint on that column to the timestamp (/getdate())

    For more information the below article from the MSDN can help.

     

    http://msdn.microsoft.com/en-us/library/ms187887.aspx

     


    Nothing is Permanent... even Knowledge....
    My Blog
    Friday, August 19, 2011 6:28 PM
  • When using SqlBulkCopy, can you retrieve the timestamp of the inserted/updated records?

    Anonymous
    • Merged by Martin_Xie Tuesday, August 23, 2011 8:49 AM Merge it to keep them into the same topic.
    Friday, August 19, 2011 6:37 PM
  • If you have a timestamp field in the table then yes.

    chanmm


    chanmm
    Saturday, August 20, 2011 1:49 AM
  •  

    Hi  phbuser,

    Welcome to MSDN Forum.

    Also thanks chanmm for his suggestion.

    It's easy to add a timestamp filed into the table and track the updated date time via T-SQL GetDate() function.

    GETDATE (Transact-SQL)

    Returns the current database system timestamp as a datetime value

    http://msdn.microsoft.com/en-us/library/ms188383.aspx

    e.g. 

    Insert into Table1(Name, ID, TimeStamp) Values("Martin", "100", GetDate())

    Update Table1 Set Name=”Mark”, TimeStamp = GetDate() Where FiledID=”100”


    Martin Xie [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, August 22, 2011 10:23 AM
  • I have a timestamp field in the db. My question is how do i retrieve the timestamp value when using SqlBulkCopy?


    Anonymous
    Tuesday, August 23, 2011 3:03 AM
  • I have a timestamp field in the db. My question is how do i retrieve the timestamp value when using SqlBulkCopy?


     

    Hi phbuser

     

    The SqlBulkCopy Class (System.Data.SqlClient) is used to load/copy data from a table to another similar table in batch. For example as below.

    During the BulkCopy operation, you can include TimeStamp filed.

    If you want to retrieve the TimeStamp value, you can iterate the SqlDataReader object.

                    while (reader.Read())

                    {

                        DateTime ts = reader.GetDateTime(3); //Assume that the TimeStamp is the 4th filed

                    }

     

    http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy(v=vs.85).aspx#Y769

     

                string connectionString = GetConnectionString();

                // Open a sourceConnection to the AdventureWorks database.

                using (SqlConnection sourceConnection = new SqlConnection(connectionString))

                {

                    sourceConnection.Open();

                    // Get data from the source table as a SqlDataReader.

                    SqlCommand commandSourceData = new SqlCommand("SELECT ProductID, Name, ProductNumber, TimeStamp FROM Production.Product;", sourceConnection);

                    SqlDataReader reader = commandSourceData.ExecuteReader();

     

                    // Open the destination connection

                    // Use SqlBulkCopy to move data from one table to the other table

                    using (SqlConnection destinationConnection = new SqlConnection(connectionString))

                    {

                        destinationConnection.Open();

                        // Set up the bulk copy object.

                        // Note that the column positions in the source

                        // data reader match the column positions in

                        // the destination table so there is no need to

                        // map columns.

                        using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnection))

                        {

                            bulkCopy.DestinationTableName = "dbo.BulkCopyDemoMatchingColumns";

                            try

                            {

                                // Write from the source to the destination.

                                bulkCopy.WriteToServer(reader);

                            }

                            catch (Exception ex)

                            {

                                Console.WriteLine(ex.Message);

                            }

                            finally

                            {

                                // Close the SqlDataReader. The SqlBulkCopy

                                reader.Close();

                            }

                        }

     

                    }

                }

     

    By the way, SQL Server Data Access Forum is more appropriate for such questions. Maybe a moderator will move it there.

    http://social.msdn.microsoft.com/Forums/en-US/sqldataaccess/threads


    Martin Xie [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, August 23, 2011 9:22 AM