出口达ta Using Bulk Insert

Bulk Insert Functionality

One way to export data from MATLAB^®and insert it into your database is to use the年代qlwritefunction 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, seeWorking with Large Data Sets.

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

Bulk Insert intoOracle

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 =“密码”; conn = database(datasource,username,password);

Create a table namedBULKTESTusing theexecutefunction.

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'};

ExpandAto 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');fori = 1:size(A,1) fprintf(fid,' % 10.2 f \ t % s \ t % s \ t % s \ n”,A{i,1},...A{i,2},A{i,3},A{i,4});endfclose(fid);
```

Set the folder location using theexecutefunction.

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

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

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

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 '...“\ t”)的终止位置(“tmp.txt”))...'REJECT LIMIT 10000']) execute(conn,'INSERT INTO BULKTEST SELECT * FROM testinsert')

Confirm the number of variables inBULKTEST.

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 intoMicrosoftSQL Server2005

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

Connect to a configured JDBC data source for theMicrosoft SQL Serverdatabase.

datasource ="MSSQLServer_JDBC"; username ="user"; password =“密码”; conn = database(datasource,username,password);

Create a table namedBULKTESTusing theexecutefunction.

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'};

ExpandAto 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 Serverhas difficulty reading files that are not on the same machine as the database.
```
fid = fopen('c:\temp\tmp.txt','wt');fori = 1:size(A,1) fprintf(fid,' % 10.2 f \ t % s \ t % s \ t % s \ n”,A{i,1},...A{i,2},A{i,3},A{i,4});endfclose(fid);
```

Run the bulk insert functionality using theexecutefunction.

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

Confirm the number of variables inBULKTEST.

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 intoMySQL

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 =“密码”; conn = database(datasource,username,password);

Create a table namedBULKTESTusing theexecutefunction.

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'};

ExpandAto 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');fori = 1:size(A,1) fprintf(fid,' % 10.2 f \ t % s \ t % s \ t % s \ n”,...A{i,1},A{i,2},A{i,3},A{i,4});endfclose(fid);
```
Run the bulk insert functionality. The SQL statement uses the statementLOCAL INFILEfor 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 inBULKTEST.

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