将excel中的数据集与SQL Server表进行比较

本文关键字:Server 比较 SQL excel 数据集 | 更新日期: 2023-09-27 18:00:51

我从mysql表中提取了如下数据集,并将其连接到Excel中。

╔════════════════════════════════════════════════════════════════╗
║ student_no     name  age subject   mark  status   updated_date ║
╠════════════════════════════════════════════════════════════════╣
║ 1              aaa   18  Subject1   50    A      2015-07-02    ║
║ 2              bbb   19  Subject1   50    A      2015-07-02    ║
║ 3              ccc   20  Subject3   60    A      2015-07-02    ║
║ 4              ddd   21  Subject4   50    A      2015-07-02    ║
║ 5              fff   22  Subject5   70    A      2015-07-02    ║
╚════════════════════════════════════════════════════════════════╝

我需要将上面数据集的每一行与我的SQL表进行比较(就像下面的查询一样(,如果SQL表中不存在数据集值的行,那么我需要将上述数据集中特定行的状态从"a"更改为"I"。

SQL查询

select 
    std.student_number, std.student_name,
    sa.subject_name, ma.mark 
from 
    student std
join 
    subject sa ON std.student_no = sa.student_no
join 
    marks ma ON sa.subject_id=ma.subject_id
where 
    (std.student_name = 'aaa' 
     and std.age = 18 
     and sa.subject = 'Subject1')
select 
    std.student_number, std.student_name,
    sa.subject_name, ma.mark 
from 
    student std
join 
    subject sa ON std.student_no = sa.student_no
join 
    marks ma ON sa.subject_id = ma.subject_id
where 
    (std.student_name = 'bbb' 
     and std.age = 19 
     and sa.subject = 'Subject1')

我需要编写任何.net应用程序才能做到这一点吗?或者我可以通过SQL Server本身完成这一点?

将excel中的数据集与SQL Server表进行比较

您应该使用SSIS(SQL Server Integration Services(创建一个项目,该项目将接收Excel文件并将其转储到新表中,然后创建一个查询,将原始表与导入新表的Excel表进行比较。