Export Data Using Bulk Insert

Bulk Insert Functionality

One way to export data from MATLAB^® and insert it into your database is to use the sqlwrite function at the command line. However, if you experience performance issues with this process, you can instead create a data file containing every record in your data set. Then, you can use this data file as input into the bulk insert functionality of your database to process the large data set. Also, with this file, you can insert data with special characters such as double quotes. A bulk insert provides performance gains by using the bulk insert utilities that are native to different database systems. For details, see Working with Large Data Sets.

The following examples use preconfigured JDBC data sources. For more information about configuring a JDBC data source, see the databaseConnectionOptions function.

Bulk Insert into Oracle

This example uses a data file containing sports data on a local machine with Oracle^® installed and exports data in the file to the Oracle server using bulk insert functionality.

Connect to a configured JDBC data source for the Oracle database.

datasource = "ORA_JDBC";
username = "user";
password = "password";
conn = database(datasource,username,password);

Create a table named BULKTEST using the execute function.

execute(conn,['CREATE TABLE BULKTEST (salary number, ' ...
    'player varchar2(25), signed varchar2(25), ' ...
    'team varchar2(25))'])

Create a data record.

A = {100000.00,'KGreen','06/22/2011','Challengers'};

Expand A to a data set containing 10,000 records.
```
A = A(ones(10000,1),:);
```
Write data to a file for bulk insert functionality.
Tip
When connecting to a database on a remote machine, you must write this file to the remote machine. Oracle has difficulty reading files that are not on the same machine as the database.
```
fid = fopen('c:\temp\tmp.txt','wt');
for i = 1:size(A,1)
    fprintf(fid,'%10.2f \t %s \t %s \t %s \n',A{i,1}, ...
    A{i,2},A{i,3},A{i,4});
end
fclose(fid);
```

Set the folder location using the execute function.

execute(conn, ...
    'CREATE OR REPLACE DIRECTORY ext AS ''C:\\Temp''')

Delete the temporary table, if it exists, using the execute function.
```
execute(conn,'DROP TABLE testinsert')
```

Create a temporary table and use bulk insert functionality to insert it into the table BULKTEST.

execute(conn,['CREATE TABLE testinsert (salary number, ' ...
    'player varchar2(25), signed varchar2(25), ' ...
    'team varchar2(25)) ORGANIZATION EXTERNAL ' ...
    '( TYPE ORACLE_LOADER DEFAULT DIRECTORY ext ACCESS ' ...
    'PARAMETERS ( RECORDS DELIMITED BY NEWLINE FIELDS ' ...
    'TERMINATED BY ''\t'') LOCATION (''tmp.txt'')) ' ...
    'REJECT LIMIT 10000'])

execute(conn,'INSERT INTO BULKTEST SELECT * FROM testinsert')

Confirm the number of variables in BULKTEST.

results = fetch(conn,'SELECT * FROM BULKTEST');
results.Properties.VariableNames

ans =

  1×4 cell array

    {'SALARY'}    {'PLAYER'}    {'SIGNED'}    {'TEAM'}

Close the database connection.
```
close(conn)
```

Bulk Insert into Microsoft SQL Server 2005

This example uses a data file containing sports data on a local machine with Microsoft^® SQL Server^® installed and exports data in the file to the Microsoft SQL Server using bulk insert functionality.

Connect to a configured JDBC data source for the Microsoft SQL Server database.

datasource = "MSSQLServer_JDBC";
username = "user";
password = "password";
conn = database(datasource,username,password);

Create a table named BULKTEST using the execute function.

execute(conn,['CREATE TABLE BULKTEST (salary ' ...
    'decimal(10,2), player varchar(25), signed_date ' ...
    'datetime, team varchar(25))'])

Create a data record.

A = {100000.00,'KGreen','06/22/2011','Challengers'};

Expand A to a data set containing 10,000 records.
```
A = A(ones(10000,1),:);
```
Write data to a file for bulk insert functionality.
Tip
When connecting to a database on a remote machine, you must write this file to the remote machine. Microsoft SQL Server has difficulty reading files that are not on the same machine as the database.
```
fid = fopen('c:\temp\tmp.txt','wt'); 
for i = 1:size(A,1)
   fprintf(fid,'%10.2f \t %s \t %s \t %s \n',A{i,1}, ...
   A{i,2},A{i,3},A{i,4});
end
fclose(fid);
```

Run the bulk insert functionality using the execute function.

execute(conn,['BULK INSERT BULKTEST FROM ' ...
    '''c:\temp\tmp.txt'' WITH (FIELDTERMINATOR = ''\t'', ' ...
    'ROWTERMINATOR = ''\n'')'])

Confirm the number of variables in BULKTEST.

results = fetch(conn,'SELECT * FROM BULKTEST');
results.Properties.VariableNames

ans =

  1×4 cell array

    {'SALARY'}    {'PLAYER'}    {'SIGNED_DATE'}    {'TEAM'}

Close the database connection.
```
close(conn)
```

Bulk Insert into MySQL

This example uses a data file containing sports data on a local machine with MySQL^® installed and exports data in the file to a MySQL database using bulk insert functionality.

Connect to a configured JDBC data source for the MySQL database.

datasource = "MySQL_JDBC";
username = "user";
password = "password";
conn = database(datasource,username,password);

Create a table named BULKTEST using the execute function.

execute(conn,['CREATE TABLE BULKTEST (salary decimal, ' ...
    'player varchar(25), signed_date varchar(25), ' ...
    'team varchar(25))'])

Create a data record.

A = {100000.00,'KGreen','06/22/2011','Challengers'};

Expand A to a data set containing 10,000 records.
```
A = A(ones(10000,1),:);
```
Write data to a file for bulk insert functionality.
Note
MySQL reads files saved locally, even if you are connecting to a remote machine. Therefore, you can write the file to either your local or remote machine.
```
fid = fopen('c:\temp\tmp.txt','wt');
for i = 1:size(A,1)
   fprintf(fid,'%10.2f \t %s \t %s \t %s \n', ...
   A{i,1},A{i,2},A{i,3},A{i,4});
end
fclose(fid);
```
Run the bulk insert functionality. The SQL statement uses the statement LOCAL INFILE for error handling. For details about this statement, consult the MySQL database documentation.
```
execute(conn,['LOAD DATA LOCAL INFILE ' ...
    ' ''C:\\temp\\tmp.txt'' INTO TABLE BULKTEST ' ...
    'FIELDS TERMINATED BY ''\t'' LINES TERMINATED ' ...
    'BY ''\n'''])
```

Confirm the number of variables in BULKTEST.

results = fetch(conn,'SELECT * FROM BULKTEST');
results.Properties.VariableNames

ans =

  1×4 cell array

    {'SALARY'}    {'PLAYER'}    {'SIGNED_DATE'}    {'TEAM'}

Close the database connection.
```
close(conn)
```

External Websites

SQL Tutorial