Skip to main content

QlikView Documents

QlikView documentation and resources.

Announcements
Document boards are being consolidated, this board no longer allows NEW documents READ MORE

Using QlikView for (bulk) INSERT into SQL

cancel
Showing results for 
Search instead for 
Did you mean: 
cheenu_janakira
Creator III
Creator III

Using QlikView for (bulk) INSERT into SQL

Attachments

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.

(A few changes on the Qlik side, please refer to the Help File here for server configuration or here in a discussion post.)

QVS from Github.

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)

Comments
MK_QSL
MVP
MVP

Good work !

cheenu_janakira
Creator III
Creator III

Cheerio. Hope it helps. Of course, in general, I wouldn't suggest using Qlik to write to database as many things can potentially go wrong, but amm hoping the 'cleanse' of special characters within the data will take care of that.

Have fun.

0 Likes
bernhard2760
Partner - Contributor III
Partner - Contributor III

Great post!

0 Likes
madhuparnadhar
Contributor III
Contributor III

The script is great and it worked as a charm for me. Thanks for your help!

0 Likes
bhavtan
Contributor II
Contributor II

does this work in qlikview server?

0 Likes
Version history
Last update:
2 weeks ago
Updated by: