Bulk Loading for MySQL

Overview

Bulk loading is the fastest way to insert large numbers of rows into a MySQL table. Using this facility instead of regular SQL insert statements, you can insert rows more rapidly.

The MySQL LIBNAME engine calls the MySQL bulk-load facility when you specify BULKLOAD=YES. You must specify BULKLOAD=YES to use this facility.

This facility uses the LOAD DATA command to create a text file on the client, which lets MySQL bulk load it into a table on the server.

Data Set Options for Bulk Loading

Here are the MySQL bulk-load data set options. For detailed information about these options, see Data Set Options for Relational Databases.

• BL_DEFAULT_DIR=
• BULKLOAD=

Examples

This example uses the DATA step.

libname x mysql user=myusr1 server=mysrv1 database=mydb1 password=mypwd1;

data x.mydata_bulk21(BL_DEFAULT_DIR="c:/temp/mysql" bulkload=yes);
col1=1;col2='Test';
run;

This next example uses PROC APPEND.

libname x mysql user=myusr1 server=mysrv1 database=mydb1 password=mypwd1;

data x;col1=1;col2='TEST';
output;
run;

proc append data=x base=x.mydata_bulk21(bulkload=yes);
run;