Exporting Data Using the Bulk Insert Command

On this page…
Bulk Insert to Oracle Bulk Insert to Microsoft SQL Server 2005 Bulk Insert to MySQL

On this page…

Bulk Insert to Microsoft SQL Server 2005

Bulk Insert to Oracle

This example demonstrates how to export data to the Oracle server using the bulk insert command. To follow this example, use a data file on the local machine where Oracle is installed.

Connect to the Oracle database.

javaaddpath 'path\ojdbc5.jar';
conn = database('databasename','user','password', ...
   'oracle.jdbc.driver.OracleDriver', ...
   'jdbc:oracle:thin:@machine:port:databasename');

Create a table named BULKTEST.

e = exec(conn,['create table BULKTEST (salary number, '...
'player varchar2(25), signed varchar2(25), '...
'team varchar2(25))']);
close(e)

Enter data records. A sample record appears as follows.
```
A = {100000.00,'KGreen','06/22/2011','Challengers'};
```
Expand A to a 10,000-record data set.
```
A = A(ones(10000,1),:);
```

Write data to a file for bulk insert.

Tip When connecting to a database on a remote machine, you must write this file to the remote machine. Oracle has problems trying to read files that are not on the same machine as the instance of 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.

e = exec(conn, ...
   'create or replace directory ext as ''C:\\Temp''');
close(e)

Delete the temporary table if it exists.

e = exec(conn,'drop table testinsert');
try,close(e),end

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

e = exec(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']);
close(e)
e = exec(conn,'insert into BULKTEST select * from testinsert');
close(e)

Confirm the number of rows and columns in BULKTEST.

e = exec(conn, 'select * from BULKTEST');
results = fetch(e)

results =
 
        Attributes: []
              Data: {10000x4 cell}
    DatabaseObject: [1x1 database]
          RowLimit: 0
          SQLQuery: 'select * from BULKTEST'
           Message: []
              Type: 'Database Cursor Object'
         ResultSet: [1x1 oracle.jdbc.driver.OracleResultSetImpl]
            Cursor: [1x1 com.mathworks.toolbox.database.sqlExec]
         Statement: [1x1 oracle.jdbc.driver.OracleStatementWrapper]
             Fetch: [1x1 com.mathworks.toolbox.database.fetchTheData]

columnnames(results)

ans =

'SALARY','PLAYER','SIGNED','TEAM'

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

Bulk Insert to Microsoft SQL Server 2005

Connect to the Microsoft SQL Server. For JDBC driver use, add the jar file to the MATLAB javaclasspath.

javaaddpath 'path\sqljdbc4.jar';
conn = database('databasename','user','password', ...
   'com.microsoft.sqlserver.jdbc.SQLServerDriver', ...
   'jdbc:sqlserver://machine:port;
   database=databasename');

Create a table named BULKTEST.

e = exec(conn,['create table BULKTEST (salary '...
'decimal(10,2), player varchar(25), signed_date '...
'datetime, team varchar(25))']);
close(e)

Enter data records. A sample record appears as follows.
```
A = {100000.00,'KGreen','06/22/2011','Challengers'};
```
Expand A to a 10,000-record data set.
```
A = A(ones(10000,1),:);
```

Write data to a file for bulk insert.

Tip When connecting to a database on a remote machine, you must write this file to the remote machine. Microsoft SQL Server has problems trying to read files that are not on the same machine as the instance of 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

Run the bulk insert.

e = exec(conn,['bulk insert BULKTEST from '...
'''c:\temp\tmp.txt''with (fieldterminator = ''\t'', '...
'rowterminator = ''\n'')']);

Confirm the number of rows and columns in BULKTEST.

e = exec(conn, 'select * from BULKTEST');
results = fetch(e)

results =
 
        Attributes: []
              Data: {10000x4 cell}
    DatabaseObject: [1x1 database]
          RowLimit: 0
          SQLQuery: 'select * from BULKTEST'
           Message: []
              Type: 'Database Cursor Object'
         ResultSet: [1x1 com.microsoft.sqlserver.jdbc.SQLServerResultSet]
            Cursor: [1x1 com.mathworks.toolbox.database.sqlExec]
         Statement: [1x1 com.microsoft.sqlserver.jdbc.SQLServerStatement]
             Fetch: [1x1 com.mathworks.toolbox.database.fetchTheData]

columnnames(results)

ans =

'salary','player','signed_date','team'

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

Bulk Insert to MySQL

Connect to the MySQL server. For JDBC driver use, add the jar file to the MATLAB javaclasspath.

javaaddpath 'path\mysql-connector-java-5.1.13-bin.jar';
conn = database('databasename', 'user', 'password', ...
   'com.mysql.jdbc.Driver', ...
   'jdbc:mysql://machine:port/databasename');

Create a table named BULKTEST.

e = exec(conn,['create table BULKTEST (salary decimal, '...
   'player varchar(25), signed_date varchar(25), '...
   'team varchar(25))']);
close(e)

Create a data record, such as the one that follows.

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

Expand A to be a 10,000-record data set.
```
A = A(ones(10000,1),:);
```

Write data to a file for bulk insert.

Note MySQL reads files saved locally, even if you are connecting to a 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. Note the use of local infile.

e = exec(conn,['load data local infile '...
   ' ''C:\\temp\\tmp.txt'' into table BULKTEST '...
   'fields terminated by ''\t'' lines terminated '...
   'by ''\n''']);
close(e)

Confirm the number of rows and columns in BULKTEST.

e = exec(conn, 'select * from BULKTEST');
results = fetch(e)

results =
 
        Attributes: []
              Data: {10000x4 cell}
    DatabaseObject: [1x1 database]
          RowLimit: 0
          SQLQuery: 'select * from BULKTEST'
           Message: []
              Type: 'Database Cursor Object'
         ResultSet: [1x1 com.mysql.jdbc.JDBC4ResultSet]
            Cursor: [1x1 com.mathworks.toolbox.database.sqlExec]
         Statement: [1x1 com.mysql.jdbc.StatementImpl]
             Fetch: [1x1 com.mathworks.toolbox.database.fetchTheData]

columnnames(results)

ans =

'salary','player','signed_date','team'

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

Exporting Multiple Records from the MATLAB Workspace

Retrieving BINARY or OTHER Sun Java SQL Data Types

Recommended Products

Get MATLAB trial software

Includes the most popular MATLAB recorded presentations with Q&A sessions led by MATLAB experts.

Get the Interactive Kit

Search MATLAB Documentation
R2012a Documentation → Database Toolbox
View documentation for other releases	Learn more about Database Toolbox