Matlab fastinsert for > 1M rows x 150 fields

Question

How would one insert 1Million + rows multiply by 150 columns from Matlab matrix into a SQL table. Matlab's Fastinsert seems inadequate for this as it takes a very long time. We're currently using SQL server database.

Our current process is write the matrix into CSV/.txt and then load this csv into a table via SSIS/dts package. Now we're looking to cut this process and directly write to SQL table.

Answer 1

It seems like the fastest way is to actually construct a big insert statement.

The example given here claims this can lead to a 100 times speedup.

%% Upload using an INSERT statement

% clear the table
exec(db,deleteQuery);

% transpose the data
allData2 = allData';

% format the input values
values = sprintf('(%f,%u,''%s''),\n',allData2{:});

% change NaNs to NULLs
values = regexprep(values,'NaN','null');

% construct the SQL INSERT statement
insertQuery = sprintf('insert into %s (%s,%s,%s) values ',tableName,fields{:});
insertQuery = [insertQuery , values(1:end-2),';'];

tic
exec(db,insertQuery);
toc

Given the size of your data you may want to chop it up a bit into smaller pieces, but it should still be possible to get much better speeds than you currently have.