将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本身完成这一点?
您应该使用SSIS(SQL Server Integration Services(创建一个项目,该项目将接收Excel文件并将其转储到新表中,然后创建一个查询,将原始表与导入新表的Excel表进行比较。