Append Data to Existing Database Table Using Insert Functionality

To append data to an existing database table, you can use the sqlwrite function. The datainsert and fastinsert functions will be removed in a future release. When using the sqlwrite function, you no longer have to preprocess or convert the data, as required by the datainsert function. The following short examples show how to append the same data using both the sqlwrite and datainsert functions. Use these examples for migrating to the sqlwrite function for data insertion.

Append data to an existing database table by using the sqlwrite function.

% Read from 'airlinesmall.csv'
impObj = detectImportOptions('airlinesmall.csv');
impObj = setvartype(impObj, ... 
    {'DepTime','ArrTime','ActualElapsedTime','CRSElapsedTime', ...
    'ArrDelay','DepDelay','Distance'},'double');

airlines_data = readtable('airlinesmall.csv',impObj);

% Insert using sqlwrite function
sqlwrite(conn,'airlinesmall',airlines_data);

Append the same data to the database table by using the datainsert function.

% Read from 'airlinesmall.csv'
impObj = detectImportOptions('airlinesmall.csv');
impObj = setvartype(impObj, ...
    {'DepTime','ArrTime','ActualElapsedTime','CRSElapsedTime', ...
    'ArrDelay','DepDelay','Distance'},'double');

airlines_data = readtable('airlinesmall.csv',impObj);
variablenames = airlines_data.Properties.VariableNames;
airlines_data = table2cell(airlines_data);

% Convert to compatible data
columns = size(airlines_data,2);
for i = 1:columns
    a = airlines_data(:,i);
    if all(cellfun(@(x)isnumeric(x),a)) == true
        a(cellfun(@isnan,a)) = {Inf};
        airlines_data(:,i) = a;
    end
end

airlines_data = cell2table(airlines_data,'VariableNames',variablenames);

% Insert using datainsert function
datainsert(conn,'airlinesmall',variablenames,airlines_data);

When using the datainsert function, you must complete additional steps to preprocess the data to insert. Use the sqlwrite function instead to avoid these extra steps.

See Also

| | | | |

Topics