如何用PHP实现Access数据库数据同步至SQL Server（更新+插入）

我来帮你搞定从Access到SQL Server的数据同步脚本，包含更新和插入逻辑。先梳理下思路，再把完整的代码给你：

你原代码里的SQL有两个小问题：SELECT子句末尾多了个逗号，UNION ALL的括号也不需要。修正后的查询语句如下：

$sql = "SELECT ID, `LAST NAME` AS lm, `FIRST NAME` AS fm FROM OLD";
$sql .= " UNION ALL SELECT ID, `Last Name` AS lm, `First Name` AS fm FROM NEW";
$sql .= " ORDER BY lm";

我们用PDO来连接SQL Server，需要确保你的PHP环境已经启用了pdo_sqlsrv扩展。连接代码示例：

// SQL Server连接配置
$sqlSrvConnStr = "sqlsrv:Server=你的SQL服务器地址;Database=目标数据库名;";
$sqlSrvUser = "用户名";
$sqlSrvPass = "密码";

try {
    $sqlSrvDbh = new PDO($sqlSrvConnStr, $sqlSrvUser, $sqlSrvPass);
    $sqlSrvDbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e) {
    die("SQL Server连接失败: " . $e->getMessage());
}

这里提供两种方案，你可以根据需求选择：

方案一：逐条判断（适合数据量不大的场景） #

遍历Access查询到的每一条数据，先检查SQL Server中是否存在相同ID的记录，存在则更新，不存在则插入：

// 遍历Access返回的每一行数据
while ($row = $sth->fetch(PDO::FETCH_ASSOC)) {
    $id = $row['ID'];
    $lastName = $row['lm'];
    $firstName = $row['fm'];

    // 先检查SQL Server中是否存在该ID
    $checkSql = "SELECT ID FROM 目标表名 WHERE ID = :id";
    $checkStmt = $sqlSrvDbh->prepare($checkSql);
    $checkStmt->bindParam(':id', $id, PDO::PARAM_INT);
    $checkStmt->execute();

    if ($checkStmt->rowCount() > 0) {
        // 存在则更新
        $updateSql = "UPDATE 目标表名 SET LastName = :lastName, FirstName = :firstName WHERE ID = :id";
        $updateStmt = $sqlSrvDbh->prepare($updateSql);
        $updateStmt->bindParam(':lastName', $lastName);
        $updateStmt->bindParam(':firstName', $firstName);
        $updateStmt->bindParam(':id', $id, PDO::PARAM_INT);
        $updateStmt->execute();
        echo "ID {$id} 已更新\n";
    } else {
        // 不存在则插入
        $insertSql = "INSERT INTO 目标表名 (ID, LastName, FirstName) VALUES (:id, :lastName, :firstName)";
        $insertStmt = $sqlSrvDbh->prepare($insertSql);
        $insertStmt->bindParam(':id', $id, PDO::PARAM_INT);
        $insertStmt->bindParam(':lastName', $lastName);
        $insertStmt->bindParam(':firstName', $firstName);
        $insertStmt->execute();
        echo "ID {$id} 已插入\n";
    }
}

方案二：使用SQL Server的MERGE语句（高效适合大数据量） #

MERGE语句可以在一次操作中完成匹配更新、不匹配插入，效率更高：

// 先把Access数据存入临时数组
$accessData = [];
while ($row = $sth->fetch(PDO::FETCH_ASSOC)) {
    $accessData[] = [
        'ID' => $row['ID'],
        'LastName' => $row['lm'],
        'FirstName' => $row['fm']
    ];
}

// 构建MERGE语句的参数化查询
if (!empty($accessData)) {
    // 构建VALUES部分
    $values = [];
    $params = [];
    foreach ($accessData as $index => $data) {
        $values[] = "( :id{$index}, :lastName{$index}, :firstName{$index} )";
        $params[":id{$index}"] = $data['ID'];
        $params[":lastName{$index}"] = $data['LastName'];
        $params[":firstName{$index}"] = $data['FirstName'];
    }

    $mergeSql = "
        MERGE INTO 目标表名 AS Target
        USING (VALUES " . implode(', ', $values) . ") AS Source (ID, LastName, FirstName)
        ON Target.ID = Source.ID
        WHEN MATCHED THEN
            UPDATE SET Target.LastName = Source.LastName, Target.FirstName = Source.FirstName
        WHEN NOT MATCHED THEN
            INSERT (ID, LastName, FirstName) VALUES (Source.ID, Source.LastName, Source.FirstName);
    ";

    $mergeStmt = $sqlSrvDbh->prepare($mergeSql);
    $mergeStmt->execute($params);
    echo "同步完成，共处理 " . count($accessData) . " 条记录\n";
}

把所有部分整合起来，记得替换掉你的实际路径、数据库信息和表名：

<?php
try {
    // 连接Access数据库
    $connStr = 'odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};' . 'Dbq=C:\myfile.accdb;';
    $accessDbh = new PDO($connStr);
    $accessDbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // 修正后的Access查询SQL
    $sql = "SELECT ID, `LAST NAME` AS lm, `FIRST NAME` AS fm FROM OLD";
    $sql .= " UNION ALL SELECT ID, `Last Name` AS lm, `First Name` AS fm FROM NEW";
    $sql .= " ORDER BY lm";
    $sth = $accessDbh->prepare($sql);
    $sth->execute();

    // 连接SQL Server数据库
    $sqlSrvConnStr = "sqlsrv:Server=你的SQL服务器地址;Database=目标数据库名;";
    $sqlSrvUser = "用户名";
    $sqlSrvPass = "密码";
    $sqlSrvDbh = new PDO($sqlSrvConnStr, $sqlSrvUser, $sqlSrvPass);
    $sqlSrvDbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // 选择同步方案（这里用方案一，你可以换成方案二）
    while ($row = $sth->fetch(PDO::FETCH_ASSOC)) {
        $id = $row['ID'];
        $lastName = $row['lm'];
        $firstName = $row['fm'];

        $checkSql = "SELECT ID FROM 目标表名 WHERE ID = :id";
        $checkStmt = $sqlSrvDbh->prepare($checkSql);
        $checkStmt->bindParam(':id', $id, PDO::PARAM_INT);
        $checkStmt->execute();

        if ($checkStmt->rowCount() > 0) {
            $updateSql = "UPDATE 目标表名 SET LastName = :lastName, FirstName = :firstName WHERE ID = :id";
            $updateStmt = $sqlSrvDbh->prepare($updateSql);
            $updateStmt->bindParam(':lastName', $lastName);
            $updateStmt->bindParam(':firstName', $firstName);
            $updateStmt->bindParam(':id', $id, PDO::PARAM_INT);
            $updateStmt->execute();
            echo "ID {$id} 已更新\n";
        } else {
            $insertSql = "INSERT INTO 目标表名 (ID, LastName, FirstName) VALUES (:id, :lastName, :firstName)";
            $insertStmt = $sqlSrvDbh->prepare($insertSql);
            $insertStmt->bindParam(':id', $id, PDO::PARAM_INT);
            $insertStmt->bindParam(':lastName', $lastName);
            $insertStmt->bindParam(':firstName', $firstName);
            $insertStmt->execute();
            echo "ID {$id} 已插入\n";
        }
    }

    // 关闭连接
    $accessDbh = null;
    $sqlSrvDbh = null;
    echo "数据同步完成！";
} catch(PDOException $e) {
    die("同步过程出错: " . $e->getMessage());
}
?>

注意事项：

• 确保你的PHP环境安装并启用了pdo_odbc（用于Access连接）和pdo_sqlsrv（用于SQL Server连接）扩展。
• 替换脚本中的你的SQL服务器地址、目标数据库名、用户名、密码、目标表名为实际信息。
• 如果Access中的ID可能重复（比如OLD和NEW表有相同ID），UNION ALL会返回重复记录，你可以改成UNION来去重，或者在同步时处理重复ID的优先级。

内容的提问来源于stack exchange，提问作者Spiris