Programmatic Data Pipelines: CSV to SQL Server using PHP and T-SQL

Manual tools like the SSMS Import Wizard are fine for one-off tasks. A production application needs automation — PHP reading uploaded files and pushing them into SQL Server without human intervention. This guide covers two approaches and the best-practice guardrails every PHP-to-SQL pipeline needs.

This guide focuses on the PHP side of the pipeline. For the SQL Server performance tuning that makes bulk loads fast — TABLOCK, recovery model switching, index management — see the SQL Server bulk loading guide. For the PostgreSQL equivalent of this pattern, see the PHP-to-Postgres pipeline guide.

Prerequisites: Installing the SQL Server PHP Drivers

Unlike MySQL, which is typically bundled with PHP, the SQL Server drivers must be installed separately. You need the Microsoft Drivers for PHP for SQL Server — specifically the pdo_sqlsrv extension added to your php.ini.

<?php
$serverName = "your_server_name";
$database   = "DataWarehouse";
$uid        = "your_username";
$pwd        = "your_password";

try {
    $conn = new PDO(
        "sqlsrv:server=$serverName;Database=$database",
        $uid,
        $pwd
    );
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
    die("Connection failed: " . $e->getMessage());
}

Method 1: PDO Transactional Loop — Balanced Performance

For small to medium CSV files (up to ~50,000 rows), prepared statements inside a transaction give you the best balance of security, validation flexibility, and speed.

<?php
// Stream the file — never load it all into memory with file()
$file   = fopen('data.csv', 'r');
$header = fgetcsv($file); // Consume and discard the header row

$sql  = "INSERT INTO SalesStaging (ProductName, Price, SaleDate)
         VALUES (?, ?, ?)";
$stmt = $conn->prepare($sql);

try {
    $conn->beginTransaction();

    while (($row = fgetcsv($file)) !== FALSE) {
        // Cast types before executing — PHP treats all CSV values as strings
        $price = (float)$row[1];
        $qty   = (int)$row[2];

        // Row-level validation: skip invalid prices
        if ($price <= 0) {
            continue;
        }

        $stmt->execute([$row[0], $price, $row[2]]);
    }

    $conn->commit();
    echo "Import successful.";
} catch (Exception $e) {
    $conn->rollBack();
    echo "Import failed: " . $e->getMessage();
}

fclose($file);

Method 2: BULK INSERT Bridge — Server-Side Speed

When the file is already on the server (or a shared network path accessible to the SQL Server service account), PHP can trigger a BULK INSERT command directly — offloading the entire load to SQL Server's native bulk-copy engine. This is significantly faster than looping through the file in PHP. For the full performance flags (TABLOCK, batch sizing, index management), see the SQL Server bulk loading guide.

<?php
// Path must be accessible to the SQL Server service account — not just PHP
$csvPath = 'C:\\Shared\\Uploads\\data.csv';

$sql = "BULK INSERT SalesStaging
        FROM '$csvPath'
        WITH (
            FORMAT          = 'CSV',
            FIRSTROW        = 2,
            FIELDTERMINATOR = ',',
            ROWTERMINATOR   = '\\n',
            TABLOCK
        )";

$conn->exec($sql);

Pipeline Best Practices

1. Always Use a Staging Table

Never import directly into your production table. Load the raw CSV into a staging table where every column is VARCHAR(MAX) — nothing fails on a type error during load. Then validate and cast in T-SQL before merging into the production schema. This is the same three-step pattern described in the staging table strategy guide — the concept translates directly to SQL Server.

2. Stream, Don't Load

Use fopen() and fgetcsv() as shown in Method 1 — never file(). The file() function reads the entire CSV into a PHP array, which for a large file can exhaust memory_limit before a single row reaches the database. Streaming keeps memory usage flat regardless of file size.

3. Cast Types Explicitly

PHP treats every value parsed by fgetcsv() as a string. Always cast before executing a prepared statement — otherwise SQL Server may reject the value or silently coerce it incorrectly:

$price    = (float)$row[1];
$quantity = (int)$row[2];
$active   = filter_var($row[3], FILTER_VALIDATE_BOOLEAN);

Troubleshooting