Welcome Guest | My Membership | Login

Using SQL Server's BULK INSERT in MVON BASIC


Article

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

 

# # #          # # #          # # #

 

Related Articles

  • Communicate with SQL - D3 Code Sample

    Database: D3

    You can using a combination of a D3 BASIC program and perl to communicate with an SQL database. This sample code will show you how you can do this.

  • Connecting SQL Server 2000 From Linux using Perl DBI

    Resource for setting up Perl and Sybase protocols on Linux/Unix so MultiValue Applications can access Microsoft SQL Server.

  • MS SQL Resources

    ARticles and resources for MS SQL for people without much SQL knowledge

  • QM and VFS

    Company: Ladybridge Systems Ltd Database: OpenQM

    Sometimes MultiValue can be the complete solution. Sometimes we just use pieces. The database can be used without using mvBASIC or Proc. Or, you can code the middle in MultiValue and let SQL hold the data. Here's a success story with QM can playing that middle role.

  • SQL Server/Management Studio

    SQL Server/Management Studio Downloads for Integrating MS SQL with MultiValue Applicaitons Webinar


Return to top