Using QlikView for (bulk) INSERT into SQL

    Hi everyone,


    Here is a file to write the content of a QVD into an MS SQL database with minimum amount of coding.

    The script uses bulk insert function in SQL and inserts 1,000 rows at a time.


    There are instructions/assumptions in the front-end of the app.


    1. Ensure the source QVD has the columns correctly formatted on the first run (as it creates the SQL table based on the coded Qlik data types - even though Qlik data types technically don't exist) as this determines the 'data types' it builds into the SQL table.
    2. Replace the existing "CONNECT TO" string to the database server.
    3. Point to the QVD you want to write to SQL.
    4. Script will change/replace all special characters in data, table and field names. Underscore will replace any 'space' in table and field names and the latter 2 will be in 'all caps' to follow DB naming-convention.
    5. Script will create a table in the database (the database itself must exist or be created prior to launching the script).
    6. Re-run the script after the script has created the target SQL table and the script will (bulk) insert 1,000 rows at a time.
    7. File creates/updates one "DB_Write_Log.qvd" QVD with stats of what has been written to the SQL table by this script/file (keeps a historical record).


    Tentative stats are: (a) Writes 1,000 rows (one insert) every 1.63 seconds for a table 7 columns deep; (b) Writes 45,637 rows in 58 seconds for a table 10 columns deep.


    Hope you find this useful.


    A video tutorial here: Qlik Bulk INSERT into SQL table - YouTube


    Have fun,


    C  ;o)