Using SQL Server's BULK INSERT in MVON BASIC
The following twelve-line MVON BASIC program builds a BULK INSERT command and passes it to SQL Server.
PROGRAM IMPORT.USING.BULK.INSERT * Build our BULK INSERT statement CSV.FILE = '\ONgroup\MVON\local.os\CSV\20151124.csv' ;* Comma-separated file FMT.FILE = '\ONgroup\MVON\local.os\CSV\CALL_REVIEW.fmt' ;* The BULK INSERT format file. * Use the \ character as our quotation mark, since both ' and " are used in the BI.SNTC. BI.SNTC = \INSERT INTO CALL_REVIEW_2 SELECT '20151124.csv' "CSV_FILE_NAME",* FROM OPENROWSET(BULK '\:CSV.FILE:\', FORMATFILE = '\:FMT.FILE:\', CODEPAGE = 'RAW',FIRSTROW =2) AS T1\ * Call -SQL.EXEC OUT.LIST = "" TERM.OUT = "" CALL -SQL.EXEC(@WHO, BI.SNTC, OUT.LIST, TERM.OUT) IF @SYSTEM.RETURN.CODE < 0 THEN CRT TERM.OUT ;* ELSE SERVER.CODE CONTAINS NUMBER OF ROWS INSERTED END ;* OF PROGRAM
What it Means
SQL Server's BULK INSERT is a great way to get tab-delimited, comma-delimited, or system-delimited data into SQL Server. MVON BASIC can drive a call, or calls, to BULK INSERT via the SQL.EXEC subroutine.
CALL -SQL.EXEC(AccountName, SQLServerCommand, ReturnList, TerminalOutput)
AccountName - An MVON account as defined in the mvonacct.dat file.
SQLServerCommand - The command to execute in the SQL Server run-time.
ReturnList - A dynamic array of the columns in the query, where each row is separated by a field mark and each column is separated by a value mark. Not used when SQLServerCommand is not a query.
TerminalOutput - The terminal output from SQL Server.
If our BULK INSERT is successful, @SYSTEM.RETURN.CODE will hold the number of rows inserted. If there is an error, @SYSTEM.RETURN.CODE will be < 0, and TERM.OUT will contain our error message.
INSERT INTO CALL_REVIEW_2 SELECT '20151124.csv' "CSV_FILE_NAME",* FROM OPENROWSET( BULK '\ONgroup\MVON\local.os\CSV\20151124.csv', FORMATFILE = '\ONgroup\MVON\local.os\CSV\CALL_REVIEW.fmt', CODEPAGE = 'RAW',FIRSTROW =2) AS T1
CALL_REVIEW_2 is a table in our SQL Server database, which is defined as follows:
CALL_REVIEW_ID is an Identity column; meaning that it gets an automatically generated sequential primary key. The name of our csv file is determined in BASIC and passed to SQL Server as a recurring literal value. The rest of the data will come from the csv file itself, in accordance with the CALL_REVIEW.fmt file.
CALL_REVIEW.fmt is a parameter file for BULK INSERT, which tells how our csv file is to be parsed, and looks like this:
11.0 13 1 SQLCHAR 0 60 "," 2 RECORDING_FILE_NAME SQL_Latin1_General_CP1_CI_AS 2 SQLCHAR 0 60 "," 3 ACCOUNT_NBR SQL_Latin1_General_CP1_CI_AS 3 SQLCHAR 0 60 "," 4 CALL_DATE SQL_Latin1_General_CP1_CI_AS 4 SQLCHAR 0 60 "," 5 CALL_TIME SQL_Latin1_General_CP1_CI_AS 5 SQLCHAR 0 10 "," 6 PHONE_DIALED SQL_Latin1_General_CP1_CI_AS 6 SQLCHAR 0 60 "," 7 AGENT_NAME SQL_Latin1_General_CP1_CI_AS 7 SQLCHAR 0 60 "," 8 SESSION SQL_Latin1_General_CP1_CI_AS 8 SQLCHAR 0 60 "," 9 CALL_RESULT SQL_Latin1_General_CP1_CI_AS 9 SQLCHAR 0 60 "," 10 AGENT_RESULT SQL_Latin1_General_CP1_CI_AS 10 SQLCHAR 0 60 "," 11 COMPAING_FILE_NAME SQL_Latin1_General_CP1_CI_AS 11 SQLCHAR 0 60 "," 12 CLIENT_ID SQL_Latin1_General_CP1_CI_AS 12 SQLCHAR 0 60 "," 13 DURATION SQL_Latin1_General_CP1_CI_AS 13 SQLCHAR 0 60 "\r\n" 14 SKILL SQL_Latin1_General_CP1_CI_AS
SQL Server BULK INSERT format files come in both the XML and non-XML variety. This one is clearly non-XML. More info can be found here: