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.
+3
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
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
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.
+3
source to share