Need to update certain columns in table from csv file

Question

I have a csv file and I need to update data in only a few columns in my sql database table. What is the best way to do this? I was thinking bulk import however, it will not let me do this without using all of the columns. I was thinking of using format file, but I wanted to know if this is the most efficient way.

Here is how I was trying it from my C# class:

/// <summary>
        /// Update all of the PropertyDefinitions
        /// </summary>
        internal static void InsertPropertyDefinitions()
        {

            //
            //  Define the connection
            //
            SqlConnection insertConnection = null;

            try
            {
                RetryStrategy retryStrategy = new Incremental(5, TimeSpan.FromMilliseconds(500), TimeSpan.FromMilliseconds(3000));
                RetryPolicy retryPolicy = new RetryPolicy<SqlDatabaseTransientErrorDetectionStrategy>(retryStrategy);

                retryPolicy.ExecuteAction(() =>
                {
                    //
                    //  Try to connect to the database
                    //
                    using (insertConnection = new SqlConnection(m_ConnectionString))
                    {
                        //
                        //  Open the connection
                        //
                        insertConnection.Open();

                        //
                        //  Get the insert command ready
                        //
                        using (SqlCommand insertRecordCmd = insertConnection.CreateCommand())
                        {
                            //
                            // Define the Insert command
                            //
                            insertRecordCmd.CommandText = @"
                            BULK INSERT dbo.[PropertyDefinition] 
                            FROM '//my file path'
                            WITH(
                                    FIRSTROW = 2,
                                    FIELDTERMINATOR = ',',
                                    ROWTERMINATOR = '\n'        
                                )
                            ";

                            // Execute the INSERT command
                            insertRecordCmd.ExecuteNonQuery();
                        }

                        insertConnection.Close();
                    }
                });
            }
            catch (Exception ex)
            {
                //
                //  This is unexpected so display full exception information
                //
                m_Log.WriteLine("Exception while creating table");
                m_Log.WriteLine(ex.Message.ToString());
                throw;
            }
        }

Bulk import into a temporary table, and then use just the columns you need from that table to update your own tables. — Ken White, Jun 22 '16 at 18:27
That is one option. The only thing that may prove difficult with this is if I am trying to continuously update this table by republishing the database and creating a new tenant. — Tim, Jun 22 '16 at 18:30
So basically I am going to send the csv file off to another team in our company. They will update some of the data, send the file back to me and then I want to be able to simply import that data into our current table. — Tim, Jun 22 '16 at 18:31
Why don't you load the CSV into memory and remove the columns you don't want. Then use SqlBulkCopy. The following SO post has an example of creating a DataTable and using SqlBulkCopy. You could modify the code to filter out columns you don't want. https://stackoverflow.com/questions/20759302/upload-csv-file-to-sql-server — William Xifaras, Jun 22 '16 at 18:42
@WilliamXifaras This worked. If you move your comment to Solution I will mark it as 'Answer'. Thanks for the link. — Tim, Jun 22 '16 at 19:47

score 1 · Accepted Answer · answered Jun 22 '16 at 20:11

A recommendation would be to put the csv data into memory and filter out the columns you don't want.

The following SO article has an example of how to populate a DataTable from a CSV and use SqlBulkCopy to bulk insert into SQL Server. You can modify the code to filter out the columns you don't want.

Upload CSV file to SQL server

Need to update certain columns in table from csv file

1 Answers1