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_collen to 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.
참고 항목
개념
Performing Bulk Copy Operations (ODBC)