0 Replies Latest reply: Jan 26, 2017 10:05 AM by Pat Yearick RSS

    Upload table without loop from QlikView to SQL Server

    Pat Yearick

      All,

       

      This works to read from one table in a SQL Server instance into a table called BAC in QV. Next it writes a couple of the imported fields to a different table line by line. But it's really slow.

       

      My attempt (below the working code) to write it all at once errors.

       

      Are there any suggestions for the correct syntax to upload the whole table at once?

       

      Thanks!

       

      Pat

       

      ***working code***

      SET ThousandSep=',';

      SET DecimalSep='.';

      SET MoneyThousandSep=',';

      SET MoneyDecimalSep='.';

      SET MoneyFormat='$#,##0.00;($#,##0.00)';

      SET TimeFormat='h:mm:ss TT';

      SET DateFormat='M/D/YYYY';

      SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

      SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

      SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

      SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';

      SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';

      SET FirstWeekDay=6;

      SET BrokenWeeks=1;

      SET ReferenceDay=0;

      SET FirstMonthOfYear=1;

      SET CollationLocale='en-US';

       

      OLEDB CONNECT TO <stuff deleted> ... Application Intent=READWRITE];

       

      SQL SELECT *

      FROM dbo.BAC;

       

      LET Nrecord = NoOfRows('BAC');

       

      sql truncate table dbo.BACTest;

       

      for i = 1 to Nrecord

       

      LET PC = FieldValue('PC',i);

      LET Zip = FieldValue('Physical postal code',i);


      sql insert into dbo.BACTest ([Profit Center Name],[Physical postal code]) values('$(PC)','$(Zip)');

       

      NEXT i

       

      ***not working code***

       

      sql insert into dbo.BACTest ([Profit Center Name],[Physical postal code]) SELECT FieldValue('PC'),FieldValue('Physical postal code') FROM BAC;