MATLAB fastinsert error

Question

I have a simple table in SqlServer database, simSQLTable. The column headings are:

index, del_date, name

I have a table in MATLAB mytbl, with one row:

num   deldate  myname
---   -------  ------
1     '07-02-2015 00:00:00.0'  'JenZ'

mycolnames = {'index', 'del_date', 'name'};

I use fastinsert like this:

fastinsert(conn, 'simSQLTable', mycolnames, mytbl);

but I get this error:

Error using database/fastinsert (line 90)
Variable fields and insert fields do not match.

How to resolve this?

Answer 1

I think the main problem here is that the column names in mycolnames pertain to the database table, not your MATLAB table mytbl. That has its own variable names given by {'num' 'deldate' 'myname'}, which don't match the database column names. From the fastinsert documentation for the data argument:

Data to insert, specified as a numeric matrix, cell array, table, dataset array, or structure that contains all data for insertion into the existing database table tablename. If data is a structure, then field names in the structure must match colnames. If data is a table or a dataset array, then the variable names in the table or dataset array must match colnames.

To get around this, you can either modify the variable names in your table so they match your database column names:

mytbl.Properties.VariableNames = mycolnames;
fastinsert(conn, 'simSQLTable', mycolnames, mytbl);

Or you can just extract the data from your table that needs to be inserted:

fastinsert(conn, 'simSQLTable', mycolnames, mytbl.Variables);

Answer 2

fastinsert queries the database to get the datatypes of the columns. This causes problems with reserved keywords. Try to use a back-tick ` in mySQL or " in Transact-SQL to quote the keyword index.

mySQL:

mycolnames = {'`index`', 'del_date', 'name'}

Transact-SQL:

mycolnames = {'"index"', 'del_date', 'name'}