SQL INSERT

[johnan]

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.

[biester]

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

[johnan]

Okay, are the any examples hor how to?

[johnan]

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.

[biester]

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

Rgds,
Joachim

[#6]

Hi

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

[#7]

Hi

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

[#8]

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

[#9]

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

[rharmsny]

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