%% Import Data from SQL Query Using Import Options
% Customize import options when importing data from the results of an SQL query on a database. Control the import options by creating an SQLImportOptions object. Then, customize import options for different columns in the SQL query. Import data using the fetch function.
% This example uses the employees_database.mat file, which contains the columns first_name, hire_date, and DEPARTMENT_NAME. The example also uses a Microsoft® SQL Server® Version 11.00.2100 database and the Microsoft SQL Server Driver 11.00.5058.
%
% Create a database connection to a Microsoft SQL Server database with Windows® authentication. Specify a blank user name and password.

datasource = 'MS SQL Server Auth';
conn = database(datasource,'','');

%
% Load employee information into the MATLAB® workspace.

employeedata = load('employees_database.mat');

% Create the employees and departments database tables using the employee information.

emps = employeedata.employees;
depts = employeedata.departments;

sqlwrite(conn,'employees',emps)
sqlwrite(conn,'departments',depts)

%
% Create an SQLImportOptions object using an SQL query and the databaseImportOptions function. This query retrieves all information for employees who are sales managers or programmers.

sqlquery = ['SELECT * from employees e join departments d ' ...
    'on (e.department_id = d.department_id) where job_id ' ...
    'in (''IT_PROG'',''SA_MAN'')'];
opts = databaseImportOptions(conn,sqlquery)

%
% Display the current import options for the variables selected in the SelectedVariableNames property of the SQLImportOptions object.

vars = opts.SelectedVariableNames;
varOpts = getoptions(opts,vars)

%
% Change the data types for the hire_date, DEPARTMENT_NAME, and first_name variables using the setoptions function. Then, display the updated import options. Because hire_date stores date and time data, change the data type of this variable to datetime. Because DEPARTMENT_NAME designates a finite set of repeating values, change the data type of this variable to categorical. Also, change the name of this variable to lowercase. Because first_name stores text data, change the data type of this variable to string.

opts = setoptions(opts,'hire_date','Type','datetime');
opts = setoptions(opts,'DEPARTMENT_NAME','Name','department_name', ...
    'Type','categorical');
opts = setoptions(opts,'first_name','Type','string');

vars = opts.SelectedVariableNames;
varOpts = getoptions(opts,vars)

%
% Select the three modified variables using the SelectVariableNames property.

opts.SelectedVariableNames = {'first_name','hire_date','department_name'};

%
% Import and display the results of the SQL query using the fetch function.

employees_data = fetch(conn,sqlquery,opts)

%
% Delete the employees and departments database tables using the execute function.

execute(conn,'DROP TABLE employees')
execute(conn,'DROP TABLE departments')

% Close the database connection.

close(conn)

% Copyright 2018 The MathWorks, Inc.