Bulk Copying from Program Variables

THIS TOPIC APPLIES TO: yesSQL ServeryesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

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.

See Also

Performing Bulk Copy Operations (ODBC)