Bulk Copying from Program Variables

You can bulk copy directly from program variables. After allocating variables to hold the data for a row and calling bcp_init to start the bulk copy, call bcp_bind for each column to specify the location and format of the program variable to be associated with the column. Fill each variable with data, then call bcp_sendrow to send one row of data to the server. Repeat the process of filling the variables and calling bcp_sendrow until all the rows have been sent to the server, then call bcp_done to specify that the operation is complete.

The bcp_bindpData parameter contains the address of the variable being bound to the column. The data for each column can be stored in one of two ways:

  • Allocate one variable to hold the data.

  • Allocate an indicator variable followed immediately by the data variable.

The indicator variable indicates the length of the data for variable-length columns, and also indicates NULL values if the column allows NULLs. If only a data variable is used, then the address of this variable is stored in the bcp_bindpData parameter. If an indicator variable is used, the address of the indicator variable is stored in the bcp_bindpData parameter. The bulk copy functions calculate the location of the data variable by adding the bcp_bindcbIndicator and pData parameters.

bcp_bind supports three methods for dealing with variable-length data:

  • Use cbData with only a data variable. Place the length of the data in cbData. Each time the length of the data to be bulk copied changes, call bcp_collento reset cbData. If one of the other two methods is being used, specify SQL_VARLEN_DATA for cbData. If all the data values being supplied for a column are NULL, specify SQL_NULL_DATA for cbData.

  • Use indicator variables. As each new data value is moved into the data variable, store the length of the value in the indicator variable. If one of the other two methods is being used, specify 0 for cbIndicator.

  • Use terminator pointers. Load the bcp_bindpTerm parameter with the address of the bit pattern that terminates the data. If one of the other two methods is being used, specify NULL for pTerm.

All three of these methods can be used on the same bcp_bind call, in which case the specification that results in the smallest amount of data being copied is used.

The bcp_bindtype parameter uses DB-Library data type identifiers, not ODBC data type identifiers. DB-Library data type identifiers are defined in sqlncli.h for use with the ODBC bcp_bind function.

Bulk copy functions do not support all ODBC C data types. For example, the bulk copy functions do not support the ODBC SQL_C_TYPE_TIMESTAMP structure, so use SQLBindCol or SQLGetData to convert ODBC SQL_TYPE_TIMESTAMP data to a SQL_C_CHAR variable. If you then use bcp_bind with a type parameter of SQLCHARACTER to bind the variable to a SQL Server datetime column, the bulk copy functions convert the timestamp escape clause in the character variable to the proper datetime format.

The following table lists the recommended data types to use in mapping from an ODBC SQL data type to a SQL Server data type.

ODBC SQLdata type

ODBC C data type

bcp_bind type parameter

SQL Server data type

SQL_CHAR

SQL_C_CHAR

SQLCHARACTER

character

char

SQL_VARCHAR

SQL_C_CHAR

SQLCHARACTER

varchar

character varying

char varying

sysname

SQL_LONGVARCHAR

SQL_C_CHAR

SQLCHARACTER

text

SQL_WCHAR

SQL_C_WCHAR

SQLNCHAR

nchar

SQL_WVARCHAR

SQL_C_WCHAR

SQLNVARCHAR

nvarchar

SQL_WLONGVARCHAR

SQL_C_WCHAR

SQLNTEXT

ntext

SQL_DECIMAL

SQL_C_CHAR

SQLCHARACTER

decimal

dec

money

smallmoney

SQL_NUMERIC

SQL_C_NUMERIC

SQLNUMERICN

numeric

SQL_BIT

SQL_C_BIT

SQLBIT

bit

SQL_TINYINT (signed)

SQL_C_SSHORT

SQLINT2

smallint

SQL_TINYINT (unsigned)

SQL_C_UTINYINT

SQLINT1

tinyint

SQL_SMALL_INT (signed)

SQL_C_SSHORT

SQLINT2

smallint

SQL_SMALL_INT (unsigned)

SQL_C_SLONG

SQLINT4

int

integer

SQL_INTEGER (signed)

SQL_C_SLONG

SQLINT4

int

integer

SQL_INTEGER (unsigned)

SQL_C_CHAR

SQLCHARACTER

decimal

dec

SQL_BIGINT (signed and unsigned)

SQL_C_CHAR

SQLCHARACTER

bigint

SQL_REAL

SQL_C_FLOAT

SQLFLT4

real

SQL_FLOAT

SQL_C_DOUBLE

SQLFLT8

float

SQL_DOUBLE

SQL_C_DOUBLE

SQLFLT8

float

SQL_BINARY

SQL_C_BINARY

SQLBINARY

binary

timestamp

SQL_VARBINARY

SQL_C_BINARY

SQLBINARY

varbinary

binary varying

SQL_LONGVARBINARY

SQL_C_BINARY

SQLBINARY

image

SQL_TYPE_DATE

SQL_C_CHAR

SQLCHARACTER

datetime

smalldatetime

SQL_TYPE_TIME

SQL_C_CHAR

SQLCHARACTER

datetime

smalldatetime

SQL_TYPE_TIMESTAMP

SQL_C_CHAR

SQLCHARACTER

datetime

smalldatetime

SQL_GUID

SQL_C_GUID

SQLUNIQUEID

uniqueidentifier

SQL_INTERVAL_

SQL_C_CHAR

SQLCHARACTER

char

SQL Server does not have signed tinyint, unsigned smallint, or unsigned int data types. To prevent the loss of data values when migrating these data types, create the SQL Server table with the next largest integer data type. To prevent users from later adding values outside the range allowed by the original data type, apply a rule to the SQL Server column to restrict the allowable values to the range supported by the data type in the original source:

CREATE TABLE Sample_Ints(STinyIntCol   SMALLINT,
USmallIntCol INT)
GO
CREATE RULE STinyInt_Rule
AS 
@range >= -128 AND @range <= 127
GO
CREATE RULE USmallInt_Rule
AS 
@range >= 0 AND @range <= 65535
GO
sp_bindrule STinyInt_Rule, 'Sample_Ints.STinyIntCol'
GO
sp_bindrule USmallInt_Rule, 'Sample_Ints.USmallIntCol'
GO

SQL Server does not support interval data types directly. An application can, however, store interval escape sequences as character strings in a SQL Server character column. The application can read them for later use, but they cannot be used in Transact-SQL statements.

The bulk copy functions can be used to quickly load data into SQL Server that has been read from an ODBC data source. Use SQLBindCol to bind the columns of a result set to program variables, then use bcp_bind to bind the same program variables to a bulk copy operation. Calling SQLFetchScroll or SQLFetch then fetches a row of data from the ODBC data source into the program variables, and calling bcp_sendrow bulk copies the data from the program variables to SQL Server .

An application can use the bcp_colptr function anytime it needs to change the address of the data variable originally specified in the bcp_bind pData parameter. An application can use the bcp_collen function anytime it needs to change the data length originally specified in the bcp_bindcbData parameter.

You cannot read data from SQL Server into program variables using bulk copy; there is nothing like a "bcp_readrow" function. You can only send data from the application to the server.