SqlBulkCopyColumnMapping Constructors
Definition
Important
Some information relates to prerelease product that may be substantially modified before it’s released. Microsoft makes no warranties, express or implied, with respect to the information provided here.
Overloads
| SqlBulkCopyColumnMapping() |
Parameterless constructor that initializes a new SqlBulkCopyColumnMapping object. |
| SqlBulkCopyColumnMapping(Int32, Int32) |
Creates a new column mapping, using column ordinals to refer to source and destination columns. |
| SqlBulkCopyColumnMapping(Int32, String) |
Creates a new column mapping, using a column ordinal to refer to the source column and a column name for the target column. |
| SqlBulkCopyColumnMapping(String, Int32) |
Creates a new column mapping, using a column name to refer to the source column and a column ordinal for the target column. |
| SqlBulkCopyColumnMapping(String, String) |
Creates a new column mapping, using column names to refer to source and destination columns. |
SqlBulkCopyColumnMapping()
Parameterless constructor that initializes a new SqlBulkCopyColumnMapping object.
public:
SqlBulkCopyColumnMapping();public SqlBulkCopyColumnMapping ();Public Sub New ()Examples
The following example bulk copies data from a source table in the AdventureWorks sample database to a destination table in the same database. Although the number of columns in the destination matches the number of columns in the source, the column names and ordinal positions do not match. SqlBulkCopyColumnMapping objects are used to create a column map for the bulk copy.
Important
This sample will not run unless you have created the work tables as described in Bulk Copy Example Setup.
This code is provided to demonstrate the syntax for using SqlBulkCopy only. If the source and destination tables are in the same SQL Server instance,
it is easier and faster to use a Transact-SQL INSERT … SELECT statement to copy the data.
using System;
using System.Data;
using Microsoft.Data.SqlClient;
class Program
{
static void Main()
{
string connectionString = GetConnectionString();
// Open a sourceConnection to the AdventureWorks database.
using (SqlConnection sourceConnection =
new SqlConnection(connectionString))
{
sourceConnection.Open();
// Perform an initial count on the destination table.
SqlCommand commandRowCount = new SqlCommand(
"SELECT COUNT(*) FROM " +
"dbo.BulkCopyDemoDifferentColumns;",
sourceConnection);
long countStart = System.Convert.ToInt32(
commandRowCount.ExecuteScalar());
Console.WriteLine("Starting row count = {0}", countStart);
// 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();
// Set up the bulk copy object.
using (SqlBulkCopy bulkCopy =
new SqlBulkCopy(connectionString))
{
bulkCopy.DestinationTableName =
"dbo.BulkCopyDemoDifferentColumns";
// Set up the column mappings by name.
SqlBulkCopyColumnMapping mapID =
new SqlBulkCopyColumnMapping("ProductID", "ProdID");
bulkCopy.ColumnMappings.Add(mapID);
SqlBulkCopyColumnMapping mapName =
new SqlBulkCopyColumnMapping("Name", "ProdName");
bulkCopy.ColumnMappings.Add(mapName);
SqlBulkCopyColumnMapping mapMumber =
new SqlBulkCopyColumnMapping("ProductNumber", "ProdNum");
bulkCopy.ColumnMappings.Add(mapMumber);
// Write from the source to the destination.
try
{
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();
}
}
// Perform a final count on the destination
// table to see how many rows were added.
long countEnd = System.Convert.ToInt32(
commandRowCount.ExecuteScalar());
Console.WriteLine("Ending row count = {0}", countEnd);
Console.WriteLine("{0} rows were added.", countEnd - countStart);
Console.WriteLine("Press Enter to finish.");
Console.ReadLine();
}
}
private static string GetConnectionString()
// To avoid storing the sourceConnection string in your code,
// you can retrieve it from a configuration file.
{
return "Data Source=(local); " +
" Integrated Security=true;" +
"Initial Catalog=AdventureWorks;";
}
}
Remarks
If you use this constructor, you must then define the source for the mapping using the SourceColumn property or the SourceOrdinal property, and define the destination for the mapping using the DestinationColumn property or the DestinationOrdinal property.
Applies to
SqlBulkCopyColumnMapping(Int32, Int32)
Creates a new column mapping, using column ordinals to refer to source and destination columns.
public:
SqlBulkCopyColumnMapping(int sourceColumnOrdinal, int destinationOrdinal);public SqlBulkCopyColumnMapping (int sourceColumnOrdinal, int destinationOrdinal);new Microsoft.Data.SqlClient.SqlBulkCopyColumnMapping : int * int -> Microsoft.Data.SqlClient.SqlBulkCopyColumnMappingPublic Sub New (sourceColumnOrdinal As Integer, destinationOrdinal As Integer)Parameters
- sourceColumnOrdinal
- Int32
The ordinal position of the source column within the data source.
- destinationOrdinal
- Int32
The ordinal position of the destination column within the destination table.
Examples
The following example bulk copies data from a source table in the AdventureWorks sample database to a destination table in the same database. Although the number of columns in the destination matches the number of columns in the source, the column names and ordinal positions do not match. SqlBulkCopyColumnMapping objects are used to create a column map for the bulk copy based on the ordinal positions of the columns.
Important
This sample will not run unless you have created the work tables as described in Bulk Copy Example Setup.
This code is provided to demonstrate the syntax for using SqlBulkCopy only. If the source and destination tables are in the same SQL Server instance, it is easier
and faster to use a Transact-SQL INSERT … SELECT statement to copy the data.
using Microsoft.Data.SqlClient;
class Program
{
static void Main()
{
string connectionString = GetConnectionString();
// Open a sourceConnection to the AdventureWorks database.
using (SqlConnection sourceConnection =
new SqlConnection(connectionString))
{
sourceConnection.Open();
// Perform an initial count on the destination table.
SqlCommand commandRowCount = new SqlCommand(
"SELECT COUNT(*) FROM " +
"dbo.BulkCopyDemoDifferentColumns;",
sourceConnection);
long countStart = System.Convert.ToInt32(
commandRowCount.ExecuteScalar());
Console.WriteLine("Starting row count = {0}", countStart);
// 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();
// Set up the bulk copy object.
using (SqlBulkCopy bulkCopy =
new SqlBulkCopy(connectionString))
{
bulkCopy.DestinationTableName =
"dbo.BulkCopyDemoDifferentColumns";
// Set up the column mappings by ordinal.
SqlBulkCopyColumnMapping columnMapID =
new SqlBulkCopyColumnMapping(0, 0);
bulkCopy.ColumnMappings.Add(columnMapID);
SqlBulkCopyColumnMapping columnMapName =
new SqlBulkCopyColumnMapping(1, 2);
bulkCopy.ColumnMappings.Add(columnMapName);
SqlBulkCopyColumnMapping columnMapNumber =
new SqlBulkCopyColumnMapping(2, 1);
bulkCopy.ColumnMappings.Add(columnMapNumber);
// Write from the source to the destination.
try
{
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();
}
}
// Perform a final count on the destination
// table to see how many rows were added.
long countEnd = System.Convert.ToInt32(
commandRowCount.ExecuteScalar());
Console.WriteLine("Ending row count = {0}", countEnd);
Console.WriteLine("{0} rows were added.", countEnd - countStart);
Console.WriteLine("Press Enter to finish.");
Console.ReadLine();
}
}
private static string GetConnectionString()
// To avoid storing the sourceConnection string in your code,
// you can retrieve it from a configuration file.
{
return "Data Source=(local); " +
" Integrated Security=true;" +
"Initial Catalog=AdventureWorks;";
}
}
Applies to
SqlBulkCopyColumnMapping(Int32, String)
Creates a new column mapping, using a column ordinal to refer to the source column and a column name for the target column.
public:
SqlBulkCopyColumnMapping(int sourceColumnOrdinal, System::String ^ destinationColumn);public SqlBulkCopyColumnMapping (int sourceColumnOrdinal, string destinationColumn);new Microsoft.Data.SqlClient.SqlBulkCopyColumnMapping : int * string -> Microsoft.Data.SqlClient.SqlBulkCopyColumnMappingPublic Sub New (sourceColumnOrdinal As Integer, destinationColumn As String)Parameters
- sourceColumnOrdinal
- Int32
The ordinal position of the source column within the data source.
- destinationColumn
- String
The name of the destination column within the destination table.
Examples
The following example bulk copies data from a source table in the AdventureWorks sample database to a destination table in the same database. Although the number of columns in the destination matches the number of columns in the source, the column names and ordinal positions do not match. SqlBulkCopyColumnMapping objects are used to create a column map for the bulk copy.
Important
This sample will not run unless you have created the work tables as described in Bulk Copy Example Setup.
This code is provided to demonstrate the syntax for using SqlBulkCopy only. If the source and destination tables are in the same SQL Server instance, it is easier and
faster to use a Transact-SQL INSERT … SELECT statement to copy the data.
using Microsoft.Data.SqlClient;
class Program
{
static void Main()
{
string connectionString = GetConnectionString();
// Open a sourceConnection to the AdventureWorks database.
using (SqlConnection sourceConnection =
new SqlConnection(connectionString))
{
sourceConnection.Open();
// Perform an initial count on the destination table.
SqlCommand commandRowCount = new SqlCommand(
"SELECT COUNT(*) FROM " +
"dbo.BulkCopyDemoDifferentColumns;",
sourceConnection);
long countStart = System.Convert.ToInt32(
commandRowCount.ExecuteScalar());
Console.WriteLine("Starting row count = {0}", countStart);
// 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();
// Set up the bulk copy object.
using (SqlBulkCopy bulkCopy =
new SqlBulkCopy(connectionString))
{
bulkCopy.DestinationTableName =
"dbo.BulkCopyDemoDifferentColumns";
// Set up the column mappings by ordinal and name.
SqlBulkCopyColumnMapping columnMapID =
new SqlBulkCopyColumnMapping(0, "ProdID");
bulkCopy.ColumnMappings.Add(columnMapID);
SqlBulkCopyColumnMapping columnMapName =
new SqlBulkCopyColumnMapping(1, "ProdName");
bulkCopy.ColumnMappings.Add(columnMapName);
SqlBulkCopyColumnMapping columnMapNumber =
new SqlBulkCopyColumnMapping(2, "ProdNum");
bulkCopy.ColumnMappings.Add(columnMapNumber);
// Write from the source to the destination.
try
{
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();
}
}
// Perform a final count on the destination
// table to see how many rows were added.
long countEnd = System.Convert.ToInt32(
commandRowCount.ExecuteScalar());
Console.WriteLine("Ending row count = {0}", countEnd);
Console.WriteLine("{0} rows were added.", countEnd - countStart);
Console.WriteLine("Press Enter to finish.");
Console.ReadLine();
}
}
private static string GetConnectionString()
// To avoid storing the sourceConnection string in your code,
// you can retrieve it from a configuration file.
{
return "Data Source=(local); " +
" Integrated Security=true;" +
"Initial Catalog=AdventureWorks;";
}
}
Applies to
SqlBulkCopyColumnMapping(String, Int32)
Creates a new column mapping, using a column name to refer to the source column and a column ordinal for the target column.
public:
SqlBulkCopyColumnMapping(System::String ^ sourceColumn, int destinationOrdinal);public SqlBulkCopyColumnMapping (string sourceColumn, int destinationOrdinal);new Microsoft.Data.SqlClient.SqlBulkCopyColumnMapping : string * int -> Microsoft.Data.SqlClient.SqlBulkCopyColumnMappingPublic Sub New (sourceColumn As String, destinationOrdinal As Integer)Parameters
- sourceColumn
- String
The name of the source column within the data source.
- destinationOrdinal
- Int32
The ordinal position of the destination column within the destination table.
Examples
The following example bulk copies data from a source table in the AdventureWorks sample database to a destination table in the same database. Although the number of columns in the destination matches the number of columns in the source, the column names and ordinal positions do not match. SqlBulkCopyColumnMapping objects are used to create a column map for the bulk copy.
Important
This sample will not run unless you have created the work tables as described in Bulk Copy Example Setup. This code is
provided to demonstrate the syntax for using SqlBulkCopy only. If the source and destination tables are in the same SQL Server instance, it is easier and faster to
use a Transact-SQL INSERT … SELECT statement to copy the data.
using Microsoft.Data.SqlClient;
class Program
{
static void Main()
{
string connectionString = GetConnectionString();
// Open a sourceConnection to the AdventureWorks database.
using (SqlConnection sourceConnection =
new SqlConnection(connectionString))
{
sourceConnection.Open();
// Perform an initial count on the destination table.
SqlCommand commandRowCount = new SqlCommand(
"SELECT COUNT(*) FROM " +
"dbo.BulkCopyDemoDifferentColumns;",
sourceConnection);
long countStart = System.Convert.ToInt32(
commandRowCount.ExecuteScalar());
Console.WriteLine("Starting row count = {0}", countStart);
// 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();
// Set up the bulk copy object.
using (SqlBulkCopy bulkCopy =
new SqlBulkCopy(connectionString))
{
bulkCopy.DestinationTableName =
"dbo.BulkCopyDemoDifferentColumns";
// Set up the column mappings by name and ordinal.
SqlBulkCopyColumnMapping columnMapID =
new SqlBulkCopyColumnMapping("ProductID", 0);
bulkCopy.ColumnMappings.Add(columnMapID);
SqlBulkCopyColumnMapping columnMapName =
new SqlBulkCopyColumnMapping("Name", 2);
bulkCopy.ColumnMappings.Add(columnMapName);
SqlBulkCopyColumnMapping columnMapNumber =
new SqlBulkCopyColumnMapping("ProductNumber", 1);
bulkCopy.ColumnMappings.Add(columnMapNumber);
// Write from the source to the destination.
try
{
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();
}
}
// Perform a final count on the destination
// table to see how many rows were added.
long countEnd = System.Convert.ToInt32(
commandRowCount.ExecuteScalar());
Console.WriteLine("Ending row count = {0}", countEnd);
Console.WriteLine("{0} rows were added.", countEnd - countStart);
Console.WriteLine("Press Enter to finish.");
Console.ReadLine();
}
}
private static string GetConnectionString()
// To avoid storing the sourceConnection string in your code,
// you can retrieve it from a configuration file.
{
return "Data Source=(local); " +
" Integrated Security=true;" +
"Initial Catalog=AdventureWorks;";
}
}
Applies to
SqlBulkCopyColumnMapping(String, String)
Creates a new column mapping, using column names to refer to source and destination columns.
public:
SqlBulkCopyColumnMapping(System::String ^ sourceColumn, System::String ^ destinationColumn);public SqlBulkCopyColumnMapping (string sourceColumn, string destinationColumn);new Microsoft.Data.SqlClient.SqlBulkCopyColumnMapping : string * string -> Microsoft.Data.SqlClient.SqlBulkCopyColumnMappingPublic Sub New (sourceColumn As String, destinationColumn As String)Parameters
- sourceColumn
- String
The name of the source column within the data source.
- destinationColumn
- String
The name of the destination column within the destination table.
Examples
The following example bulk copies data from a source table in the AdventureWorks sample database to a destination table in the same database. Although the number of columns in the destination matches the number of columns in the source, the column names and ordinal positions do not match. SqlBulkCopyColumnMapping objects are used to create a column map for the bulk copy.
Important
This sample will not run unless you have created the work tables as described in Bulk Copy Example Setup.
This code is provided to demonstrate the syntax for using SqlBulkCopy only. If the source and destination tables are in the same SQL Server instance, it is easier and faster to use a
Transact-SQL INSERT … SELECT statement to copy the data.
using System;
using System.Data;
using Microsoft.Data.SqlClient;
class Program
{
static void Main()
{
string connectionString = GetConnectionString();
// Open a sourceConnection to the AdventureWorks database.
using (SqlConnection sourceConnection =
new SqlConnection(connectionString))
{
sourceConnection.Open();
// Perform an initial count on the destination table.
SqlCommand commandRowCount = new SqlCommand(
"SELECT COUNT(*) FROM " +
"dbo.BulkCopyDemoDifferentColumns;",
sourceConnection);
long countStart = System.Convert.ToInt32(
commandRowCount.ExecuteScalar());
Console.WriteLine("Starting row count = {0}", countStart);
// 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();
// Set up the bulk copy object.
using (SqlBulkCopy bulkCopy =
new SqlBulkCopy(connectionString))
{
bulkCopy.DestinationTableName =
"dbo.BulkCopyDemoDifferentColumns";
// Set up the column mappings by name.
SqlBulkCopyColumnMapping mapID =
new SqlBulkCopyColumnMapping("ProductID", "ProdID");
bulkCopy.ColumnMappings.Add(mapID);
SqlBulkCopyColumnMapping mapName =
new SqlBulkCopyColumnMapping("Name", "ProdName");
bulkCopy.ColumnMappings.Add(mapName);
SqlBulkCopyColumnMapping mapMumber =
new SqlBulkCopyColumnMapping("ProductNumber", "ProdNum");
bulkCopy.ColumnMappings.Add(mapMumber);
// Write from the source to the destination.
try
{
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();
}
}
// Perform a final count on the destination
// table to see how many rows were added.
long countEnd = System.Convert.ToInt32(
commandRowCount.ExecuteScalar());
Console.WriteLine("Ending row count = {0}", countEnd);
Console.WriteLine("{0} rows were added.", countEnd - countStart);
Console.WriteLine("Press Enter to finish.");
Console.ReadLine();
}
}
private static string GetConnectionString()
// To avoid storing the sourceConnection string in your code,
// you can retrieve it from a configuration file.
{
return "Data Source=(local); " +
" Integrated Security=true;" +
"Initial Catalog=AdventureWorks;";
}
}