topic AW:SQL INSERT in QlikView

SQL INSERT

johnan — Fri, 11 Sep 2009 14:26:33 GMT

Hi,

I want to use "Insert into" tables from QV. Does QV9 have this or is there any macros?

I want to insert 7 columns from my table in QV into the database.

I've tried to find in forum but no good luck.

AW:SQL INSERT

biester — Fri, 11 Sep 2009 14:32:03 GMT

AFAIK there's no "insert into" in QlikView standard. If you want to write back to the database you have to do it by a macro. Another approach might be executing a stored procedure from script.

Rgds,
Joachim

AW:SQL INSERT

johnan — Fri, 11 Sep 2009 14:46:58 GMT

Okay, are the any examples hor how to?

AW:SQL INSERT

johnan — Fri, 11 Sep 2009 14:49:18 GMT

Anyone tested this?

Dynamic Data Update

For the first time in QlikView history, it is possible to
programmatically update field data in real-time without
running the script. Via new APIs and more or less standard
SQL insert, update and delete commands, any QlikView
field data can be updated directly in RAM. On a server the
updated data is pushed out to the clients.
This functionality is freely available in stand-alone
QlikView but requires an additional license on QlikView
Server. See the QlikView API Guide for more information.

AW:Re: AW:SQL INSERT

biester — Fri, 11 Sep 2009 14:53:20 GMT

Dynamic Data Update does NOT modify databases or (datasources in general); this is only DML for the QV data in memory!

Rgds,
Joachim

SQL INSERT

Fri, 28 May 2010 16:08:06 GMT

I am too wanting to do "write back" to my SQL-database (In this case Teradata, where our EDW resides).

We are currently on QV 8.5, and the Insert Into -syntax is supported in a very simple form, like:

ODBC

CONNECT TO COOPDW (XUserId is ...

SQL INSERT

INTO TRN_A_D_DK.WLt_Temp_Experim_QvImp_A values (2, 'textA', 'textB');

Does anyone know if it is possible to use variables read from a QV-source instead of the constant values in the example above ?

//Bo

SQL INSERT

Fri, 28 May 2010 16:17:49 GMT

As you can not write back into a DB directly from QV what you can do is create a text file with SQL commands and e.g. execute sqlplus to run the updates for you.

Regards

Jürg

SQL INSERT

Fri, 28 May 2010 17:57:05 GMT

The best way i know to write into sql tables is using ADO objects within a macro.

the ADO method is a very easy and flexible way to read and write into db tables.

There are a lot of examples on the web how to use it within vbs.

here is an basic example:

sub update_table

set objconnection = creatobject("ADODB.Connection")

set objrecordset = creatobject ("ADODB.Recordset")

objConnection.Open Provider=SQLOLEDB.1;Persist Security Info=True;User ID=yadb1_user;Initial Catalog=yadb1;Data Source=xx.xx.xx.xx;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=xxxxxxx;Use Encryption for Data=False;Tag with column collation when possible=False;pwd=1q2w3e;"

objRecordset.Open "table1", objConnection, adOpenDynamic,3

objRecordset.addnew

objRecordset.Fields.Item("ID")= 123

end sub

Re: SQL INSERT

Fri, 04 Nov 2011 13:23:01 GMT

With OLEDB CONNECTION and SQLSERVER this work:

LET Nrecord = NoOfRows('Tabella');

sql truncate table Auth.dbo.test;

for i = 1 to Nrecord

LET id = FieldValue('id',i);
LET value = FieldValue('value',i);

sql insert into Auth.dbo.test values('$(id)','$(value)');

NEXT i

AW:SQL INSERT

rharmsny — Fri, 18 May 2012 13:32:41 GMT

Just saw this post - you can actually use a text file for Qlikview to interpret SQL insert/update commands using a load script. Just set the right permissions in the document for updating databases and have each line preceded by an SQL.

Example: insertfile.txt

sql insert into employee (val1, val2);

sql insert into employee (val1, val2)';

Have the right connector before the following line:

incudee drive:\path\insertfile.txt

Each line will be inserted or update. You can also generate directories of sql statement text files and use a filelist loop to process all the sql as individual includes - the output looks like this:

OLEDB CONNECT*Provider*XPassword*

5/18/2012 9:01:02 AM: 0018 for each vfilefound in filelist('E:\qv\datasource\testing\inprocess\*.sql')

5/18/2012 9:01:02 AM: 0020 SQL Update REPORT_DEV.STG_ENVCOMPARE_STATUS SET etc;

5/18/2012 9:01:56 AM: 0002 SQL Update REPORT_DEV.STG_ENVCOMPARE_STATUS SET etc.

5/18/2012 9:01:57 AM: 0003 SQL Update REPORT_DEV.STG_ENVCOMPARE_STATUS SET etc.

next filefound

Re: AW:SQL INSERT

mountaindude — Wed, 15 Aug 2012 12:30:58 GMT

No need to use the $(Include=e:\temp\insert_test.txt); syntax unless you really have a reason to.

You can simply write your SQL insert statements in the QV load script, along these lines:

ODBC CONNECT TO postgres_server1;

SQL INSERT INTO table1 VALUES ('2012-08-01', 'SE', 345, 0.345);

SQL INSERT INTO table1 VALUES ('2012-08-01', 'SE', 456, 0.456);

DISCONNECT;

The above has been verified to work with PostgreSQL, using their standard ODBC 64 bit Unicode driver.

Please note however that there is a readonly check box in the ODBC driver settings. Obviously, it should not be checked...

Seems there is a bug in the help file relating to this. In QV11 SR1 the help text for "SQL" states:

"Sending SQL statements which update the database will return an error, since QlikView per definition accesses all ODBC sources in read-only mode."

AFAICT that is simply not true, at least not when connecting to PostgreSQL databases.

/Göran