/**********************************************************************
* FILENAME :    TVPCopyTable.c
*
* DESCRIPTION :
*               This example copies one table to another and illustrates
*               the use of a Table-Valued Parameter to pass several records
*               as one parameter to a stored procedure to perform the insert.
*
*               Records are read from the source table and saved into an
*               array until a batch of records is assembled. The array in
*               which the batch of records is saved is also used as a
*               Table-Valued Parameter for the insert. It is suggested,
*               table-valued parameters perform well for inserting upto
*               1000 rows but can only be used as READONLY input and DML
*               operations such as INSERT, DELETE and UPDATE cannot be
*               performed on them.
*
*               The source and destination tables both have an identity field.
*               To maintain consistency during the copy, the stored procedure
*               performing the insert issues a SET IDENTITY_INSERT ON prior to
*               the insert and a SET IDENTITY_INSERT OFF afterwards. This
*               enables write access to the identity field and stops SQL
*               Server from generating one automatically. This then allows the
*               identity column values from the source records to be copied
*               into the destination records and in so doing make an identical
*               copy.
*
* ODBC USAGE :
*               The TVP used here represents a table type with 5 columns:
*                   PersonID, FirstName, LastName, Address and City.
*
*               Prompts user for batch size
*
*               Sets up the test by dropping and re-creating:-
*                   1. Database Table     (TestTVPTable2) - into which rows
*                      are written from rows in a TVP.
*                   2. Stored Procedure   (InsertFromTVP) - to select records
*                      from the TVP and insert them into TestTVPTable1.
*                   3. TVP Table Variable (PersonTVP)     - Table variable to
*                      hold records and passed as a single parameter to
*                      InsertFromTVP().
*
*               Bind Parameters:
*                  Binds parameter 1 as a TVP
*
*               Bind TVP Parameters:
*                  Sets focus on parameter 1
*                  Bind arrays to each of the 5 TVP columns and lengths. i.e.
*                  for PersonID, FirstName, LastName, Address and City
*                  Clears focus by setting focus to zero
*
*               Prompts user for batch size.
*               Loops until all records retrieved
*               gets next record batch from source table using nextBatch()
*               write batch via TVP (i.e. all records via 1 table type
*               parameter)
*/

#include <stdio.h>
#include <stdlib.h>
#include <sql.h>
#include <sqlext.h>
#include <sqlncli.h>
#include <string.h>

#include "util.c"

#define LASTNAME_LEN  255
#define FIRSTNAME_LEN 255
#define ADDRESS_LEN 255
#define CITY_LEN  255

#define MAX_ARRAY_SIZE 20
#define MAX_BATCH_SIZE 5

// Row 0 across the arrays is used to store each record as it is
// fetched from the table. It is then moved into the part of the
// array which forms the Table-Valued Parameter (i.e. row 1 onwards);
SQLUINTEGER uiPersonID [MAX_ARRAY_SIZE+1];
SQLCHAR     strFirstName [MAX_ARRAY_SIZE+1][FIRSTNAME_LEN];
SQLCHAR     strLastName[MAX_ARRAY_SIZE+1][LASTNAME_LEN];
SQLCHAR     strAddress[MAX_ARRAY_SIZE+1][ADDRESS_LEN];
SQLCHAR     strCity[MAX_ARRAY_SIZE+1][CITY_LEN];

//
// Arrays of column values lengths. Same rule for row 0
//
SQLLEN      lPersonID[MAX_ARRAY_SIZE+1];
SQLLEN      lFirstName[MAX_ARRAY_SIZE+1];
SQLLEN      lLastName[MAX_ARRAY_SIZE+1];
SQLLEN      lAddress[MAX_ARRAY_SIZE+1];
SQLLEN      lCity[MAX_ARRAY_SIZE+1];


SQLHSTMT hstmtSrc = SQL_NULL_HSTMT;  	// Statement handle for source table
//
// SetupTVPTest is used to create and/or drop the stored procedure,
// TVP table type and DB Table used in the test.
//
SQLRETURN SetupTVPTest (SQLHDBC hdbc, char createEm, char dropEm) {

    SQLHSTMT hstmt = SQL_NULL_HSTMT;  	// Statement handle
    SQLRETURN retcode = SQL_SUCCESS;	// Return status

    // Drop statements

    // The stored procedure takes one parameter, the persons
    // Table-Value Parameter Table.
    // DROP PROCEDURE
    SQLCHAR strDropTVPProc [] =
        "IF EXISTS (SELECT * FROM dbo.sysobjects WHERE "
        "id = object_id(N'dbo.InsertFromTVP') AND "
        "OBJECTPROPERTY(id, N'IsProcedure') = 1) "
        "DROP PROCEDURE dbo.InsertFromTVP";

    // CREATE PROCEDURE
    // Note: 1. READONLY is mandatory
    //       2. Use of SET IDENTITY_INSERT allows identity
    //          column to have value inserted rather than
    //          autogenerated, making exact copy possible.
	SQLCHAR strCreateTVPProc [] =
        "CREATE PROCEDURE InsertFromTVP (@Persons PersonTVP READONLY) "
        "AS "
            "SET IDENTITY_INSERT TestTVPTable2 ON;"
            "INSERT INTO TestTVPTable2 "
            "(PersonID, FirstName, LastName, Address, City) "
            "SELECT vPersonID, vFirstName, vLastName, vAddress, vCity "
            "FROM @Persons;"
            "SET IDENTITY_INSERT TestTVPTable2 OFF";

    // The Table-Valued Parameter PersonTVP
    // DROP TVP
    SQLCHAR strDropTVPType[] =
        "IF EXISTS (SELECT * FROM sys.types WHERE "
        "is_table_type = 1 AND name = 'PersonTVP') "
        "DROP TYPE dbo.PersonTVP";

    // CREATE TVP
    SQLCHAR strCreateTVPType [] =
        "CREATE TYPE PersonTVP AS TABLE ( "
            "vPersonID   int,"
            "vFirstName  varchar(255),"
            "vLastName   varchar(255),"
            "vAddress    varchar(255),"
            "vCity       varchar(255)"
        ")";

    // The physical database table into which records are copied
    // DROP TABLE
    SQLCHAR strDropTVPTable [] =
        "IF EXISTS (SELECT * FROM dbo.sysobjects WHERE "
        "id = object_id(N'dbo.TestTVPTable2') AND "
        "OBJECTPROPERTY(id, N'IsUserTable') = 1) "
        "DROP TABLE dbo.TestTVPTable2";

    // CREATE TABLE
    SQLCHAR strCreateTVPTable [] =
        "CREATE TABLE TestTVPTable2 ("
            "PersonID int NOT NULL IDENTITY(1,1),"
            "FirstName   varchar(255),"
            "LastName    varchar(255),"
            "Address     varchar(255),"
            "City        varchar(255)"
        ")";

    // Allocate a statement handle
    retcode=SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
    CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_STMT)",
                hstmt, SQL_HANDLE_STMT);

    // DROP sequence is : Procedure, Table Type Parameter, Table
    if (dropEm=='Y') {
    	// Execute Drop Procedure
    	retcode = SQLExecDirect (hstmt, strDropTVPProc, SQL_NTS);
        CHECK_ERROR(retcode, "SQLExecDirect(DROPPROC)",
                    hstmt, SQL_HANDLE_STMT);

    	// Execute Drop Type
    	retcode = SQLExecDirect (hstmt, strDropTVPType, SQL_NTS);
        CHECK_ERROR(retcode, "SQLExecDirect(DROPTYPE)",
                    hstmt, SQL_HANDLE_STMT);

    	// Execute DROP Table
    	retcode = SQLExecDirect (hstmt, strDropTVPTable, SQL_NTS);
        CHECK_ERROR(retcode, "SQLExecDirect(DROPTABLE)",
                    hstmt, SQL_HANDLE_STMT);
    }

    // CREATE sequence is : Table, Table Type Parameter, Procedure
    if (createEm=='Y')
        // Execute Create Table
        retcode = SQLExecDirect (hstmt, strCreateTVPTable, SQL_NTS);
        CHECK_ERROR(retcode, "SQLExecDirect(CREATETABLE)",
                    hstmt, SQL_HANDLE_STMT);

        // Execute Create Type
        retcode = SQLExecDirect (hstmt, strCreateTVPType, SQL_NTS);
        CHECK_ERROR(retcode, "SQLExecDirect(CREATETYPE)",
                    hstmt, SQL_HANDLE_STMT);

        // Execute Create Procedure
        retcode = SQLExecDirect (hstmt, strCreateTVPProc, SQL_NTS);
        CHECK_ERROR(retcode, "SQLExecDirect(CREATEPROC)",
                    hstmt, SQL_HANDLE_STMT);
exit:

    // Free statement handle
    if (hstmt != SQL_NULL_HSTMT)
        SQLFreeHandle(SQL_HANDLE_STMT, hstmt);

    return retcode;
}

// Assume src table is TestTBL1, i.e with same layout as TestTDB2
int nextBatch (SQLHDBC hdbc, int lastID, int batchSize) {

    SQLRETURN retcode=SQL_SUCCESS;

    SQLLEN  indicator, rowCount;
    SQLSMALLINT columns;
    int currRec=0;

    //
    // Use keyword TOP to restrict number of rows to the batch size.
    // (differs from DB to DB. SQL SERVER/MSAccess uses TOP, MySQL
    // uses LIMIT and ORACLE uses ROWNUM). Order by PersonID is
    // important so each batch reads from where thw last one finished.
    //
    char  *stmtStr="SELECT TOP %i * FROM TestTBL1 WHERE PersonID > %i "
    "ORDER BY PersonID";

    char   stmt[128];

    // First call?
    if (hstmtSrc==SQL_NULL_HSTMT) {

        lastID=0;

        // Allocate a statement handle for selecting
        // records from the source table TestTBL1
        retcode=SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmtSrc);
        CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_STMT)",
                    hstmtSrc, SQL_HANDLE_STMT);
    }

    // Row 0 is used for records being fetched from the source table
    // Row 1 upwards is where they are moved and where the TVP is
    // mapped to.
    retcode = SQLBindCol(hstmtSrc, 1, SQL_C_LONG,   &uiPersonID[0],
                         0,             &lPersonID[0]);
    retcode = SQLBindCol(hstmtSrc, 2, SQL_C_CHAR,   strFirstName[0],
                         FIRSTNAME_LEN, &lFirstName[0]);
    retcode = SQLBindCol(hstmtSrc, 3, SQL_C_CHAR,   strLastName[0],
                         LASTNAME_LEN,  &lLastName[0]);
    retcode = SQLBindCol(hstmtSrc, 4, SQL_C_CHAR,   strAddress[0],
                         ADDRESS_LEN,   &lAddress[0]);
    retcode = SQLBindCol(hstmtSrc, 5, SQL_C_CHAR,   strCity[0],
                         CITY_LEN,      &lCity[0]);

    sprintf (stmt, stmtStr, batchSize, lastID);
    retcode = SQLExecDirect (hstmtSrc, stmt, SQL_NTS);
    CHECK_ERROR(retcode, "SQLExecDirect(SELECT TOP)",
                hstmtSrc, SQL_HANDLE_STMT);

    do {
        // SQLNumResultCols() returns number of columns in result set.
        // if non zero use SQLFetch until SQL_NO_DATA returned
        retcode=SQLNumResultCols(hstmtSrc, &columns);
      	CHECK_ERROR(retcode, "SQLNumResultCols()",
                    hstmtSrc, SQL_HANDLE_STMT);
        if (columns > 0) {
            while (SQLFetch(hstmtSrc) != SQL_NO_DATA) {
                currRec++;
                uiPersonID[currRec]=uiPersonID[0];
                strcpy (strFirstName[currRec], strFirstName[0]);
                strcpy (strLastName[currRec],  strLastName[0]);
                strcpy (strAddress[currRec],   strAddress[0]);
                strcpy (strCity[currRec],      strCity[0]);
            }
        }
    } while (SQLMoreResults(hstmtSrc) == SQL_SUCCESS);

exit:

    return currRec;
}


int main () {

    SQLHENV  henv     = SQL_NULL_HENV;  // Environment
    SQLHDBC  hdbc     = SQL_NULL_HDBC;  // Connection handle
    SQLHSTMT hstmtDst = SQL_NULL_HSTMT; // Statement handle for dest table
    SQLRETURN retcode;			// Return status

    //
    // batchSize is the number of rows stored in the TVP
    // lastPersonID is a source record identity value and is used as the
    // starting point for each batch by selecting records with identity
    // greater than the last one we had. Works if select done uses ORDER BY
    // identity field.
    //
    int  i, batchSize, rowsFound, lastPersonID=0;

    // The name of the TVP table data type
    SQLCHAR *TVPTableName = (SQLCHAR *) "PersonTVP";
    SQLLEN  lTVPRowsUsed;

    // Get number of records to read from
    getInt ("Batch Size ", &batchSize, 'N', 0);
    if (batchSize==0) goto exit;
    if (batchSize>MAX_ARRAY_SIZE) batchSize=MAX_ARRAY_SIZE;

    // Allocate environment
    retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
    CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_ENV)",
                henv, SQL_HANDLE_ENV);

    // Set ODBC Version
    retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION,
                            (SQLPOINTER*)SQL_OV_ODBC3, 0);
    CHECK_ERROR(retcode, "SQLSetEnvAttr(SQL_ATTR_ODBC_VERSION)",
                henv, SQL_HANDLE_ENV);

    // Allocate connection
    retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
    CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_DBC)",
                henv, SQL_HANDLE_DBC);

    // Set Login Timeout
    retcode = SQLSetConnectAttr(hdbc, SQL_LOGIN_TIMEOUT, (SQLPOINTER)5, 0);
    CHECK_ERROR(retcode, "SQLSetConnectAttr(SQL_LOGIN_TIMEOUT)",
                hdbc, SQL_HANDLE_DBC);

    // Connect to DSN
    retcode = SQLConnect(hdbc, (SQLCHAR*) "SQLSRV2014", SQL_NTS,
                         (SQLCHAR*) NULL, 0, NULL, 0);
    CHECK_ERROR(retcode, "SQLConnect(DSN:SQLSRV2014;)",
                hdbc, SQL_HANDLE_DBC);

    // Allocate Statement Handle
    retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmtDst);
    CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_STMT)",
                hstmtDst, SQL_HANDLE_STMT);

    // Setup stored procedure, Table-Value Parameter data type and database
    // Table.
    // Second parameter indicates whether the above are to be created
    // Third parameter indicates whether the above are to be dropped
    retcode = SetupTVPTest (hdbc, 'Y', 'Y');
    CHECK_ERROR(retcode, "SetupTVPTest()", hdbc, SQL_HANDLE_DBC);

    //
    // 1st step is bind all parameters in normal way.
    // Here we have just one parameter, a Table-Valued Parameter (TVP)
    // whish requires some specific parameters.
    //
    retcode = SQLBindParameter(
                hstmtDst,           // Statement handle
                1,                  // Parameter Number
                SQL_PARAM_INPUT,    // Input/Output Type (always INPUT for TVP)
                SQL_C_DEFAULT,      // C - Type (always this for a TVP)
                SQL_SS_TABLE,       // SQL Type (always this for a TVP)
                MAX_ARRAY_SIZE,     // For a TVP this is max rows we will use
                0,                  // For a TVP this is always 0
                TVPTableName,       // For a TVP this is the type name of the
                                    // TVP, and also a token returned by
                                    // SQLParamData.
                SQL_NTS,            // For a TVP this is the length of the type
                                    // name or SQL_NTS.
                &lTVPRowsUsed);     // For a TVP this is the number of rows
                                    // actually available.

    //
    // Now we need to bind a separate set of parameters, specifically for the
    // TVP. To do we set the focus on each TVP in turn, bind data to it in the
    // same way as for rows in a table.
    //

    // First set focus on the TVP, here it is param 1.
    retcode = SQLSetStmtAttr(hstmtDst, SQL_SOPT_SS_PARAM_FOCUS,
                             (SQLPOINTER) 1, SQL_IS_INTEGER);

    // Col 1 - Bind start of PersonID array
    retcode = SQLBindParameter(hstmtDst, 1, SQL_PARAM_INPUT,
                               SQL_C_LONG, SQL_INTEGER, 0, 0,
                               &uiPersonID[1], 0, &lPersonID[1]);

    // Col 2 - Bind start of FirstName array
    retcode = SQLBindParameter(hstmtDst, 2, SQL_PARAM_INPUT,
                               SQL_C_CHAR, SQL_CHAR,
                               FIRSTNAME_LEN, 0, &strFirstName[1],
                               FIRSTNAME_LEN, &lFirstName[1]);

    // Col 3 - Bind start of LastName array
    retcode = SQLBindParameter(hstmtDst, 3, SQL_PARAM_INPUT,
                               SQL_C_CHAR, SQL_CHAR,
                               LASTNAME_LEN, 0, &strLastName[1],
                               LASTNAME_LEN, &lLastName[1]);

    // Col 4 - Bind start of Address array
    retcode = SQLBindParameter(hstmtDst, 4, SQL_PARAM_INPUT,
                               SQL_C_CHAR, SQL_CHAR,
                               ADDRESS_LEN, 0, &strAddress[1],
                               ADDRESS_LEN, &lAddress[1]);

    // Col 5 - Bind start of City array
    retcode = SQLBindParameter(hstmtDst, 5, SQL_PARAM_INPUT,
                               SQL_C_CHAR, SQL_CHAR,
                               CITY_LEN, 0, &strCity[1],
                               CITY_LEN, &lCity[1]);

    //
    // If there were further TVPs we would set the focus to the next one,
    // bind buffers to that, and so on on until all TVPs have bound buffers.
    // As we have no more, reset param focus to zero.
    //
    retcode = SQLSetStmtAttr(hstmtDst, SQL_SOPT_SS_PARAM_FOCUS,
                             (SQLPOINTER) 0, SQL_IS_INTEGER);

    //
    // The data for each row of the TVP is represented by the nth element of
    // the arrays bound for columns FirstName, LastName, Address and City,
    // held in strFirtstName[n], strLastName[n], strAddress[n], and strCity[n]
    // Prompt for data, filling columns and rows until batchSize reached.
    // Data for the TVP starts in array posn 1 (0 is used for reading the
    // records from the source table).
    //
    retcode=SQL_SUCCESS;

    //
    // While exits via a break when no more rows to copy
    //
    while (1) {

        // Get next batch of records starting from last PersonID processed
        rowsFound=nextBatch (hdbc, lastPersonID, batchSize);

        //
        // Display the batch returned and at the same time set the length
        // variables that were bound in the SQLBindParameter() calls for the
        // TVP.
        //
        for (i=1;i<=rowsFound;i++) {

            // Person ID and length
            printf ("\n%i, ",  uiPersonID[i]);
            lPersonID[i]=sizeof(uiPersonID[i]);

            // FirstName and length
            printf ("%.10s, ", strFirstName[i]);
            lFirstName[i]=strlen(strFirstName[i]);

            // LastName and length
            printf ("%.10s, ", strLastName[i]);
            lLastName[i]=strlen(strLastName[i]);

            // Address and length
            printf ("%.10s, ", strAddress[i]);
            lAddress[i]=strlen(strAddress[i]);

            // City and length
            printf ("%.10s",   strCity[i]);
            lCity[i]=strlen(strCity[i]);

            // Remember current person ID. The PersonIDs are selected in
            // numeric ascending order (because of the ORDER BY in the select).
            // By keeping hold of the last one in this batch the next batch
            // starts at the next PersonID greater than this.
            lastPersonID=uiPersonID[i];
        }

        //
        // We now have arrays of PersonIDs, FirstNames, Lastnames, Addresses
        // and Cities with corresponding lengths for each.
        //
        // Set the StrLen_or_IndPtr value (lTVPRowsUsed) to the actual number
        // of rows to write (i.e. in the buffers) and execute the stored
        // procedure. Note this might be less than the batch size if this batch
        // is the last batch. Although just one parameter is passed, because
        // it is a TVP, several rows (like a table) can be passed as one
        // parameter (hence Table-Valued Parameter).
        //
        lTVPRowsUsed=rowsFound;
        retcode = SQLExecDirect(hstmtDst, "{CALL InsertFromTVP(?)}", SQL_NTS);
        CHECK_ERROR(retcode, "SQLExecDirect(InsertFromTVP)",
                    hstmtDst, SQL_HANDLE_STMT);

        // Got last batch? If so break out of the loop, otherwise continue
        if (rowsFound<batchSize) break; else printf ("\n");
    }

exit:

    printf ("\nComplete.\n");

    // Free handles
    // Statement
    if (hstmtSrc != SQL_NULL_HSTMT)
        SQLFreeHandle(SQL_HANDLE_STMT, hstmtSrc);

    if (hstmtDst != SQL_NULL_HSTMT)
        SQLFreeHandle(SQL_HANDLE_STMT, hstmtDst);

    // Connection
    if (hdbc != SQL_NULL_HDBC) {
        SQLDisconnect(hdbc);
        SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
    }

    // Environment
    if (henv != SQL_NULL_HENV)
        SQLFreeHandle(SQL_HANDLE_ENV, henv);

    return 0;
}

See Also


Oracle is a registered trademark of Oracle Corporation and/or its affiliates.