CODEWITHSUNDEEP
sql-server
user3569267
user3569267

Reputation: 1125

Bulk insert on specific columns in sql server

is there a way to execute bulk insert on a specific columns, i.e. if the column number of the file don't match with the column number of the table.

I want to import a file with the following schema :

Customer_ID,Customer_Name,Customer_Adress

in 'Customer' Table having the following schema:

Customer_ID,Customer_Name,Customer_Adress, Customer_Phone,Customer_email

is it possible?

Thanks

Upvotes: 2

Views: 3111

Answers (2)

Greg Oks
Greg Oks

Reputation: 2730

Maybe SqlBulkCopy might help you and then you can do something like that:

using (SqlBulkCopy bulkCopy = new SqlBulkCopy(db.ConnectionString))
            {
                bulkCopy.DestinationTableName = "Customer";
                bulkCopy.ColumnMappings.Add("Customer_ID", "Customer_ID");
                bulkCopy.ColumnMappings.Add("Customer_Name", "Customer_Name");

                DataTable dt = GetDataFromExcel(); // Get your data from the excel file

                dt.Columns[0].ColumnName = "Customer_ID";
                dt.Columns[1].ColumnName = "Customer_Name";

                bulkCopy.WriteToServer(dt);
            }

Upvotes: 1

James Z
James Z

Reputation: 12317

As far as I know you can do that using a XML format file, and probably also using a view as the target.

FORMATFILE ='format_file_path'

Specifies the full path of a format file. A format file describes the data file that contains stored responses created by using the bcp utility on the same table or view. The format file should be used if:

  • The data file contains greater or fewer columns than the table or view.
  • The columns are in a different order.
  • The column delimiters vary.

https://msdn.microsoft.com/en-us/library/ms188365.aspx

Upvotes: 3

Related Questions