Matlab fastinsert for> 1M rows x 150 fields

How would I insert 1Million + rows multiplied by 150 columns from Matlab matrix into SQL table. Matlab Fastinsert seems inadequate for this as it takes a very long time. We are currently using a SQL Server database.

Our current process is writing the matrix to CSV / .txt and then loading that csv into the table via SSIS / dts package. Now we want to shorten this process and write the SQL table directly.


source to share

1 answer

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

the example given here states that this can result in a 100x speedup.

%% Upload using an INSERT statement

% clear the table

% 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),';'];



Considering the size of your data, you might be able to chop it up a bit into smaller chunks, but you can still get much better speeds than currently.



All Articles