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)

Arguments:

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.

BULK INSERT:

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:

https://msdn.microsoft.com/en-us/library/ms191479.aspx