LoadData

The LoadData sample illustrates using SQLPrepare and SQLExecute to insert large amounts of data into Microsoft® SQL Server™ tables.

LoadData is a general-purpose utility for loading data not bound by native data formats or character restrictions onto a server running an instance of SQL Server.

The sample illustrates:

• Using arrays of parameters for rapid execution of RPC batches.
  
  
• Using manual-commit mode to break batches into units of work.
  
  
• Using data-at-execution parameters to insert values into SQL Server text and image columns.

To build the application, you must ensure that 3.x versions of the ODBC header files and libraries are used, and that the SQL Server 2000 version of Odbcss.h is used.

The sample is a Microsoft Foundation Class dialog application. The application allows you to connect to a defined ODBC SQL Server data source and requires that you enter the name of a command file to process.

The command files of the LoadData sample application allow the user to tailor command processing by using the application. The application recognizes two commands:

• ScriptRun, which processes Transact-SQL statements.
  
  
• LoadData, which executes a prepared INSERT statement with parameters to copy data to a server running SQL Server.

The application treats strings enclosed in brackets ([]) as progress text and displays them in its progress pane as the application processes a command file.

Sample data, containing a command file, script file, and ANSI text data files are included.

Command File Syntax

[text]
ScriptRun "file_name"
LoadData "database..table", "file_name"

Arguments

[text]

Is progress text. Text between the enclosing brackets is displayed in the dialog box within its progress group.

ScriptRun "file_name"

Attempts to open and read the text file indicated in the file_name parameter. The text file must contain ODBC or Transact-SQL. The application processes multiple lines of text as a single batch, using SQLExecDirect to execute the SQL batch when the string "go" is located on a single line of the file.

LoadData "database..table", "file_name"

Copies data from the client file, specified in the file_name parameter, to the SQL Server table specified in the database..table parameter.

To run the loaddata sample

1. Build a .cmd file containing ScriptRun and LoadData commands. For an example, see the file Ldnorthw.cmd in this directory:

   C:\Program Files\Microsoft SQL Server\80\Tools\Devtools\Samples\ODBC\Data\North
   

2. Run the sample by changing to the directory with the command file and specifying the samples name on the command prompt:

   cd C:\Program Files\Microsoft SQL Server\80\Tools\Devtools\Samples\ODBC\loaddata\Debug
   Loaddata

   When Loaddata.exe starts, connect to an ODBC data source, specify the location of the command file in the CommandFile window, and then click GO.

Functions Illustrated

SQLAllocHandle	SQLDriverConnect	SQLParamData
SQLBindParameter	SQLExecDirect	SQLPrepare
SQLCloseCursor	SQLExecute	SQLPutData
SQLColAttribute	SQLFreeHandle	SQLSetConnectAttr
SQLDataSources	SQLGetDiagField	SQLSetEnvAttr
SQLDescribeCol	SQLGetDiagRec	SQLSetStmtAttr
SQLDisconnect	SQLNumResultCols