4 Replies Latest reply: Nov 22, 2016 10:23 AM by David G RSS

    Insert all records from resident table to SQL db

    Jean-Pierre Bakhache

      Hi all,

       

      Is it possible to insert all records from a resident table into an db table from qlikview script and in 1 shot?

       

      I checked other posts, a loop on the records was suggested (for...next) and an insert statement would be executed every time.

      But this method is too heavy and is taking too much time.

       

      Any alternative to achieve this?

       

      Thanks

        • Re: Insert all records from resident table to SQL db
          Colin Albert

          Save the table to csv using

               STORE tablename into filename.csv (txt) ;

           

          Then import the data into your database.

           

          Qlik is designed to load data from a database not to insert data into a database.

          • Re: Insert all records from resident table to SQL db
            Gysbert Wassenaar
            Is it possible to insert all records from a resident table into an db table from qlikview script and in 1 shot?

            No. You'll need more shots. One for exporting the table to a csv file and one to import the csv file in your database. Perhaps your dbms supports csv files as virtual tables. That could make it a bit easier.

            • Re: Insert all records from resident table to SQL db
              Jean-Pierre Bakhache

              Thanks for your quick replies guys gwassenaar colin_albert

               

              I c, based on your answers, I'm also checking how we could automate the import of the csv doc...

              Any suggestion ?

               

              I searched a bit and found about the bulk insert command.

              The client will be testing it through qlikview script (to automate the process store csv + write back to db).

              Fingers crossed

               

              But what do you think? Theoretically it should work, no ?

              • Re: Insert all records from resident table to SQL db
                David G

                Hi,

                 

                I had the same problem, but with by making a string of the variables and inserting 1000 rows at a time, decreasing the load time exponentaily. Due to some of the values in the fields I hade to swith between ' for delimiter and Chr(39).

                 

                populateObj:

                LOAD [client_id],

                  [obj_id],

                  [objval_id],

                  [descr],

                  [valid_from_isodate],

                  [valid_to_isodate],

                  [active],

                  [created],

                  [row_id],

                    '('&if(isnull([client_id]),'NULL',[client_id]) &', '&

                  if(isnull([obj_id]), 'NULL',chr(39)&[obj_id]&chr(39)) &', '&

                  if(isnull([objval_id]),'NULL',chr(39)&[objval_id]&chr(39)) &', '&

                  if(isnull([descr]), 'NULL',chr(39)&[descr]&chr(39))&', '&

                  if(isnull([valid_from_isodate]), 'NULL',chr(39)&[valid_from_isodate]&chr(39)) &', '&

                  if(isnull([valid_to_isodate]), 'NULL',chr(39)&[valid_to_isodate]&chr(39))&', '&

                  if(isnull([active]),'NULL',[active])&', '&

                  if(isnull([created]),'NULL', chr(39)&[created]&chr(39))&', '&

                  if(isnull([row_id]),'NULL', [row_id])&')' as SQL_queryRowString,

                    RowNo() as RowNo,

                    Round(RowNo()-500,1000) as RowNo1000

                FROM [lib://xxxxxx (xxxx)/xxxxxx.qvd]

                (qvd);

                 

                 

                NoConcatenate

                 

                 

                tempTable:

                Load

                RowNo1000,

                Concat(SQL_queryRowString, ',') as SQLQueryValues1000

                Resident populateObj

                Group by RowNo1000;

                 

                Drop table populateObj;

                 

                LIB CONNECT TO 'BizView (ghh_qliktech)';

                 

                SQL DELETE FROM "bxxxxxxx".xxxxxxx;

                 

                 

                For vRowNo = 1 to NoOfRows('tempTable')

                    

                Let vSQLInsertValues = Peek('SQLQueryValues1000',vRowNo-1,'tempTable');

                LET vInsertedRows = vRowNo * 1000;//Used to see the loop progress

                 

                  SQL INSERT INTO "xxxxxx".xxxxxxxx (

                    client_id,

                    obj_id,

                    objval_id,

                    descr,

                    valid_from_isodate,

                    valid_to_isodate,

                    active,

                    created,

                    row_id

                  ) VALUES

                  $(vSQLInsertValues);

                 

                 

                  TRACE Inserting $(vInsertedRows) rows; //Used to see the loop progress

                   

                Next vRowNo

                 

                 

                Drop Table tempTable;

                 

                Hope it helps!