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.
- 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.
- Replace the existing "CONNECT TO" string to the database server.
- Point to the QVD you want to write to SQL.
- 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.
- Script will create a table in the database (the database itself must exist or be created prior to launching the script).
- 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.
- 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)
