基于CSV补全SQL Server与PostgreSQL客户表空白/0值的技术问询

我来分享一套贴合你当前项目需求的实操流程，从数据导出、补全到批量更新一步到位：

一、先导出含异常值的客户数据到CSV #

针对两个数据库分别写查询，精准筛选出有空白或0值的记录，方便后续补全：

SQL Server 导出查询 #

打开SSMS运行这个查询，然后用「导出向导」把结果存成CSV：

SELECT 
    customer_id, -- 唯一标识，务必保留
    customer_name,
    COALESCE(phone, '待补全') AS phone,
    COALESCE(email, '待补全') AS email,
    COALESCE(address, '待补全') AS address,
    CASE WHEN age = 0 THEN '待补全' ELSE CAST(age AS VARCHAR) END AS age
FROM 
    customers
WHERE 
    phone IS NULL OR phone = '' OR
    email IS NULL OR email = '' OR
    address IS NULL OR address = '' OR
    age = 0;

PostgreSQL 导出查询 #

可以用PGAdmin可视化导出，或者用psql命令行直接导出：

SELECT 
    customer_id,
    customer_name,
    COALESCE(phone, '待补全') AS phone,
    COALESCE(email, '待补全') AS email,
    COALESCE(address, '待补全') AS address,
    CASE WHEN age = 0 THEN '待补全' ELSE age::TEXT END AS age
FROM 
    customers
WHERE 
    phone IS NULL OR phone = '' OR
    email IS NULL OR email = '' OR
    address IS NULL OR address = '' OR
    age = 0;

命令行导出示例：

\copy (SELECT ...) TO '/your/local/path/customer_missing_data.csv' WITH (FORMAT CSV, HEADER, DELIMITER ',');

二、CSV补全的关键注意事项 #

• 死守customer_id：这个是后续匹配更新的唯一依据，绝对不能修改或遗漏
• 统一格式：比如手机号加区号、邮箱要符合标准格式、地址尽量补全到街道层级，避免后续再返工
• 效率技巧：用Excel的「筛选」功能快速定位所有标记为「待补全」的单元格，批量处理

三、批量更新脚本（安全高效版） #

补全CSV后，不要直接单条更新，用临时表导入再批量更新，既高效又能避免出错：

SQL Server 批量更新步骤 #

-- 1. 创建临时表，结构和补全后的CSV对应
CREATE TABLE #temp_customers (
    customer_id INT PRIMARY KEY,
    phone VARCHAR(20),
    email VARCHAR(100),
    address VARCHAR(255),
    age INT
);

-- 用SSMS的「导入数据」工具把补全后的CSV导入这个临时表

-- 2. 批量更新原表，只覆盖需要补全的字段
UPDATE c
SET 
    phone = CASE WHEN t.phone <> '' THEN t.phone ELSE c.phone END,
    email = CASE WHEN t.email <> '' THEN t.email ELSE c.email END,
    address = CASE WHEN t.address <> '' THEN t.address ELSE c.address END,
    age = CASE WHEN t.age <> 0 THEN t.age ELSE c.age END
FROM 
    customers c
JOIN 
    #temp_customers t ON c.customer_id = t.customer_id;

-- 3. 清理临时表
DROP TABLE #temp_customers;

PostgreSQL 批量更新步骤 #

-- 1. 创建临时表，PostgreSQL临时表会话结束后自动删除
CREATE TEMP TABLE temp_customers (
    customer_id INT PRIMARY KEY,
    phone VARCHAR(20),
    email VARCHAR(100),
    address VARCHAR(255),
    age INT
);

-- 导入补全后的CSV
\copy temp_customers FROM '/your/local/path/completed_customer_data.csv' WITH (FORMAT CSV, HEADER, DELIMITER ',');

-- 2. 批量更新原表，保留原有正确数据
UPDATE customers c
SET 
    phone = COALESCE(NULLIF(t.phone, ''), c.phone),
    email = COALESCE(NULLIF(t.email, ''), c.email),
    address = COALESCE(NULLIF(t.address, ''), c.address),
    age = CASE WHEN t.age <> 0 THEN t.age ELSE c.age END
FROM 
    temp_customers t
WHERE 
    c.customer_id = t.customer_id;

四、必做：验证更新结果 #

更新完一定要跑个查询确认所有异常值都被补全了：

SELECT 
    customer_id, phone, email, address, age
FROM 
    customers
WHERE 
    phone IS NULL OR phone = '' OR
    email IS NULL OR email = '' OR
    address IS NULL OR address = '' OR
    age = 0;

如果返回空结果，说明补全成功；如果还有记录，检查是不是CSV里漏补了。

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