ODBC/Methods/Bulk Insert

To insert more than one record, you can use either of the following 3 methods. Here we insert some random records into tdata. Note that data are in a columnar form similar to that returned by ddfch.

  len=. 1e5       NB. insert 100000 rows
  data=. ((len,5)$'A''BCDEF');((len,1)$'MF');((len,4)$'E101E201');((len,16)$'{d ''1991-02-13''}');((len,16)$'{d ''2008-12-03''}');(,. 1+i.len)

ddins

A select statement is executed to obtain a cursor, but actually no record will be retrieved.

  r=. ch ddins~ 'select NAME, SEX, DEPT, DOB, DOH, SALARY from tdata';data

Finally, we read the record again, to ensure the update was successful:

    smoutput 'number of records inserted', ":ddcnt ch
    sh=. ch ddsel~ 'select * from tdata where DOH=#2008-12-03#' do.
    smoutput ddfet sh,5
    ddend sh

+-----+-+----+----------+----------+-++
|A'BCD|M|E101|1991-02-13|2008-12-03|1||
+-----+-+----+----------+----------+-++
|EFA'B|F|E201|1991-02-13|2008-12-03|2||
+-----+-+----+----------+----------+-++
|CDEFA|M|E101|1991-02-13|2008-12-03|3||
+-----+-+----+----------+----------+-++
|'BCDE|F|E201|1991-02-13|2008-12-03|4||
+-----+-+----+----------+----------+-++
|FA'BC|M|E101|1991-02-13|2008-12-03|5||
+-----+-+----+----------+----------+-++

Parameterized Query

A parameterized query is a query that contains some unknown values when the statement is prepared, For example

   sql=. 'insert into tdata(NAME, SEX, DEPT, DOB, DOH, SALARY) values (?,?,?,?,?,?)'

The RDBMS engine can not infer types of input parameters, so their types have to provided by client applications.

ddparm

Types (and optionally lengths) together data are appended to SQL statement when using ddparm. For example

  r=. ch ddparm~ sql;((3#SQL_VARCHAR),(2#SQL_TYPE_DATE),SQL_INTEGER);data

ddsparm

This is a smarter version of ddparm in that data types will be guessed by ODBC drivers, so data types are not needed. This is applicable to queries on a single table only. If it fails, your should try ddparm instead.

  r=. ch ddsparm~ sql;data

• speed of bulk insert depends very much on capacity of individual RDBMS. Some are very fast while others are very slow.
• ddparm and ddsparm also support update and delete query.
• ddparm and ddsparm do not support queries that return records, eg. select statement.