Exporting 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.

You learn to use the following Database Toolbox™ functions:

For details about these functions, see matlab\toolbox\database\dbdemos\dbinsertdemo.m.

  1. Connect to the data source, dbtoolboxdemo, if needed:

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

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

  2. Use setdbprefs to set the format for retrieved data to numeric:

    setdbprefs('DataReturnFormat','numeric')
    
  3. Import ten rows of data the March column of data from the salesVolume table.

    curs = exec(conn, 'select March from salesVolume');
    curs = fetch(curs);
    
  4. Assign the data to the MATLAB workspace variable AA:

    AA = curs.Data
    AA =
    
    981
    1414
    890
    1800
    2600
    2800
    800
    1500
    1000
    821
  5. Calculate the sum of the March sales and assign the result to the variable sumA:

    sumA = sum(AA(:))
    sumA =
       
       14606
  6. Assign the month and sum of sales to a cell array to export to a database. Put the month in the first cell of exdata:

    exdata(1,1) = {'March'}
    exdata = 
    		'March'
    

    Put the sum in the second cell of exdata:

    exdata(1,2) = {sumA}
    exdata = 
        'March'    [14606]
    
  7. Define the names of the columns to which to export data. In this example, the column names are Month and salesTotal, from the yearlySales table in the dbtoolboxdemo database. Assign the cell array containing the column names to the variable colnames:

    colnames = {'Month','salesTotal'};
    
  8. Use the get function to determine the current status of the AutoCommit database flag. This status determines whether the exported data is automatically committed to the database. If the flag is off, you can undo an update; if it is on, data is automatically committed to the database.

    get(conn, 'AutoCommit')
    ans =
    	on
    

    The AutoCommit flag is set to on, so the exported data is automatically committed to the database.

  9. Use the fastinsert function to export the data into the yearlySales table. Pass the following arguments to this function:

    • conn, the connection object for the database

    • yearlySales, the name of the table to which you are exporting data

    • The cell arrays colnames and exdata

    fastinsert(conn, 'yearlySales', colnames, exdata)
    

    fastinsert 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. Close the cursor.

    close(curs)
    
  12. Continue with the next example (Replacing Existing Database Data with Exported Data). To stop now and resume working with the next example at a later time, close the connection.

    close(conn)
Was this topic helpful?