📥 SQL LOAD DATA / BULK INSERT – High-Speed Data Import Guide

🧲 Introduction – Why Use LOAD DATA or BULK INSERT?

Manually inserting data row-by-row using INSERT statements is fine for small jobs. But when you’re dealing with large datasets, such as CSVs or flat files with millions of rows, you need high-performance options like LOAD DATA (MySQL) or BULK INSERT (SQL Server).

These commands allow you to load data in bulk directly into a SQL table—saving time, reducing load, and improving efficiency.

🎯 In this guide, you’ll learn:

• How to use LOAD DATA in MySQL
• How to use BULK INSERT in SQL Server
• Format requirements and file structure
• Best practices and performance tips

🔄 1. LOAD DATA INFILE in MySQL

LOAD DATA INFILE '/path/to/file.csv'
INTO TABLE employees
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

✅ Explanation:

• INTO TABLE employees: Target table
• FIELDS TERMINATED BY ',': CSV delimiter
• ENCLOSED BY '"': Handles quoted strings
• IGNORE 1 ROWS: Skips the header row

💡 Tip: File path must be accessible to the database server and local file permissions must allow reading.

📦 2. BULK INSERT in SQL Server

BULK INSERT employees
FROM 'C:\data\employees.csv'
WITH (
  FIELDTERMINATOR = ',',
  ROWTERMINATOR = '\n',
  FIRSTROW = 2
);

✅ Explanation:

• BULK INSERT: Loads data into a SQL Server table
• FIRSTROW = 2: Starts from second row (skip header)
• FIELDTERMINATOR / ROWTERMINATOR: Specify delimiters

⚠️ Note: You may need to enable Ad Hoc Distributed Queries or configure BULKADMIN permissions.

📊 3. File Format Expectations

🧪 4. Real-World Use Case – Employee Import

🗃️ CSV File Example (employees.csv)

id,name,position,salary
1,John Doe,Manager,75000
2,Jane Smith,Developer,65000

✅ SQL Server

BULK INSERT employees
FROM 'C:\import\employees.csv'
WITH (
  FIELDTERMINATOR = ',',
  ROWTERMINATOR = '\n',
  FIRSTROW = 2
);

✅ MySQL

LOAD DATA INFILE '/import/employees.csv'
INTO TABLE employees
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

⚙️ 5. Performance Tips for Bulk Loading

🛡️ 6. Security Considerations

• MySQL may disable LOAD DATA LOCAL by default for security reasons.
• SQL Server may restrict bulk inserts without proper permissions (BULKADMIN role).
• File paths must be accessible to the database service (local or remote execution may vary).

🧠 7. LOAD vs INSERT – Performance Comparison

📌 Summary – Recap & Next Steps

Using LOAD DATA and BULK INSERT dramatically improves performance when importing large datasets. These tools are essential for ETL pipelines, initial data loads, and batch data migration.

🔍 Key Takeaways:

• LOAD DATA (MySQL) and BULK INSERT (SQL Server) are optimized for importing files.
• Specify field and row delimiters carefully.
• Always validate data files and permissions before loading.

⚙️ Real-World Relevance:
Used in data warehousing, reporting systems, and legacy migrations to move data at scale without heavy scripting.

❓ FAQ – SQL LOAD DATA / BULK INSERT

❓ Can I skip the header row in CSV during import?

✅ Yes:

• MySQL: IGNORE 1 ROWS
• SQL Server: FIRSTROW = 2

❓ What permissions do I need for BULK INSERT?

✅ You must be in the BULKADMIN role or have appropriate file access permissions in SQL Server.

❓ What’s the difference between LOAD DATA and INSERT INTO ... SELECT?

✅ LOAD DATA reads from files, while INSERT INTO ... SELECT copies between tables.

❓ Does LOAD DATA INFILE work remotely?

✅ Only if LOCAL is enabled (LOAD DATA LOCAL INFILE), and client/server both support it.

❓ How can I prevent duplicate rows?

✅ Use:

• IGNORE to skip duplicates
• REPLACE to overwrite duplicates (MySQL)