ODBC/Methods/Bulk Insert
ODBC: Data Sources | Handles | Data Driver | Error Messages | API: Connect Read Update Create Bulk Insert Config
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.