LOAD DATA Statement LOAD DATA INFILE ’data.txt’ INTO TABLE db2.my_table; The server also uses the non- LOCAL  rules to locate  .sdi  files for the  IMPORT TABLE  statement. For a non- LOCAL  load operation, the server reads a text file located on the server host, so these security requirements must be satisified: For a  LOCAL  load operation, the client program reads a text file located on the client host. Because the file contents are sent over the connection by the client to the server, using  LOCAL  is a bit slower than when the server accesses the file directly. On the other hand, you do not need the  FILE  privilege, and the file can be located in any directory the client program can access. The  REPLACE  and  IGNORE  modifiers control handling of new (input) rows that duplicate existing table rows on unique key values ( PRIMARY KEY  or  UNIQUE  index values): The  LOCAL  modifier has the same effect as  IGNORE . This occurs because the server has no way to stop transmission of the file in the middle of the operation. If none of  REPLACE ,  IGNORE , or  LOCAL  is specified, an error occurs when a duplicate key value is found, and the rest of the text file is ignored. In addition to affecting duplicate-key handling as just described,  IGNORE  and  LOCAL  also affect error handling: To ignore foreign key constraints during the load operation, execute a  SET foreign_key_checks = 0 statement before executing  LOAD DATA . If you use  LOAD DATA  on an empty  MyISAM  table, all nonunique indexes are created in a separate batch (as for  REPAIR TABLE ). Normally, this makes  LOAD DATA  much faster when you have many indexes. In some extreme cases, you can create the indexes even faster by turning them off with  ALTER TABLE ... Note Security Requirements Duplicate-Key and Error Handling Index Handling •You must have the  FILE  privilege. See Section 6.2.2, “Privileges Provided by MySQL” . •The operation is subject to the  secure_file_priv  system variable setting: •If the variable value is a nonempty directory name, the file must be located in that directory. •If the variable value is empty (which is insecure), the file need only be readable by the server. •With  REPLACE , new rows that have the same value as a unique key value in an existing row replace the existing row. See Section 13.2.9, “REPLACE Statement” . •With  IGNORE , new rows that duplicate an existing row on a unique key value are discarded. For more information, see The Effect of IGNORE on Statement Execution. •With neither  IGNORE  nor  LOCAL , data-interpretation errors terminate the operation. •With  IGNORE  or  LOCAL , data-interpretation errors become warnings and the load operation continues, even if the SQL mode is restrictive. For examples, see Column Value Assignment. 2642