Upload table without loop from QlikView to SQL Server

Thu, 26 Jan 2017 15:05:54 GMT

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;

topic Upload table without loop from QlikView to SQL Server in Integration, Extension & APIs

Upload table without loop from QlikView to SQL Server