Export Data to New Record in Database

This example does the following:

  1. Retrieves sales data from a salesVolume table.

  2. Calculates the sum of sales for 1 month.

  3. Stores this data in a cell array.

  4. Exports this data to a yearlySales table.

This example assumes that you are connecting to a Microsoft® Access™ database that contains tables named salesVolume and yearlySales. The table salesVolume contains the column names for each month. The table yearlySales contains the column names Month and salesTotal.

To access the code for this example, see matlab\toolbox\database\dbdemos\dbinsertdemo.m.

  1. Create a database connection conn to the Microsoft Access database. For example, the following code assumes that you are connecting to a data source named dbtoolboxdemo with blank user name and password.

    conn = database('dbtoolboxdemo','','');

    Alternatively, you can use the native ODBC interface for an ODBC connection. For details, see database.

  2. Set the format for retrieved data to numeric by using setdbprefs.

  3. Execute the SQL query string sqlquery using conn to import data for the March column from the salesVolume table. The cursor object curs contains the executed query. Import the data from the executed query using the fetch function.

    sqlquery = 'select March from salesVolume';
    curs = exec(conn,sqlquery);
    curs = fetch(curs);
  4. The Data property of curs contains the imported data. Assign the data to the MATLAB® workspace variable AA. Display the data.

    AA = curs.Data
    AA =
  5. Calculate the sum of the March sales. Assign the result to the MATLAB workspace variable sumA. Display the sum.

    sumA = sum(AA(:))
    sumA =
  6. To export the data to the database, assign the month and sum of sales to a cell array. Put the month in the first cell of cell array exdata. Put the sum in the second cell of exdata.

    exdata(1,1) = {'March'};
    exdata(1,2) = {sumA}
    exdata = 
        'March'    [14606]
  7. Define the names of the columns. Assign the cell array containing the column names to the MATLAB workspace variable colnames.

    colnames = {'Month','salesTotal'};
  8. Determine the status of the AutoCommit database flag using get. This status determines if the exported data automatically commits to the database. If the flag is off, you can undo an insert. If the flag is on, data automatically commits to the database.

    ans =

    The AutoCommit flag is set to on. The exported data automatically commits to the database.

  9. Export the data into the yearlySales table using these arguments:

    • Database connection conn

    • Table name yearlySales

    • Column names colnames

    • Export data exdata


    datainsert appends the data as a new record at the end of the yearlySales table.

  10. In Microsoft Access, view the yearlySales table to verify the results.

  11. After you finish working with the cursor object, close it.

  12. Close the database connection.


See Also

| |

Related Examples

More About

Was this topic helpful?