try: insert into C select a.part_number, a.description,b.description as E_desc,(case when a.description=b.description then 'S' else 'D' end) as D1, a.vendor,b.vendor as E_vendor,(case when a.vendor=b.vendor then 'S' else 'D' end) as D2, a.vendor_part,b.vendor_part as E_vendor_part,(case when a.vendor_part=b.vendor_part then 'S' else 'D' end) as D3, vendor_part_status,b.vendor_part_status as E_vendor_part_status,(case when a.vendor_part_status=b.vendor_part_status then 'S' else 'D' end) as D4, a.restriction,b.restriction as E_restriction,(case when a.restriction=b.restriction then 'S' else 'D' end) as D5, a.restriction_code,b.restriction_code as E_restriction_code,(case when a.restriction_code=b.restriction_code then 'S' else 'D' end) as D5 from a inner join b on a.part_number=b.part_number
insert into c select a.PARTNUMBER as Part_number,a.Description as Description,b.Description as E_desc,(case when a.Description=b.Description then 'S' else 'D' end) as D1 a.VENDOR as Vendor,b.vendor as E_vendor,(case when a.VENDOR=b.vendor then 'S' else 'D' end) as D2, a.VENDOR_PART as Vendor_partnum,b.Vendor_part as E_vendor_partnum,(case when a.VENDOR_PART=b.Vendor_part then 'S' else 'D' end) as D3, a.VENDOR_PART_STATUS as Vendor_partstatus,b.Vendor_part_status as E_vendor_partstatus,(case when a.VENDOR_PART_STATUS=b.Vendor_part_status then 'S' else 'D' end) as D4, a.RESTRICTION as Restriction,b.Restriction as E_restriction,(case when a.RESTRICTION=b.Restriction then 'S' else 'D' end) as D5, a.RESTRICTIONCODE as Res_code,b.Restrictioncode as E_res_code,(case when a.RESTRICTIONCODE=b.Restrictioncode then 'S' else 'D' end) as D6 from a inner join b on a.PART_NUMMBER=b.EMC_Part 一直报错~~
Column name or number of supplied values does not match table definition.
我不知道你的re在哪儿,不过可以这样检查: select part_number,'Not match' as re from a where not exists(select 1 from b where part_number=a.part_number)
2.是更新还是插入?
3.看不出A表和B表有什么关系,根据A的number查B的description的依据是什么?
4.貌似用不着再到程序里转一圈,写个一存储过程应该能解决问题.
2 由A表查询B表,结果插入C表中,并判断查询列是否相同。
3 同一个number 查询A,B中的description,并插入C,判断是否一样。
4 不会存储过程,number并不唯一的情况下怎么建立?
在查到的这两列相等时,比较两个discription是否相等吗?
插入C表,是相等插,还是不等插?不等插的话,插A的还是B的?
只插入description列吗?如果不是,还要插入哪些列?
只要有规则,在那儿做都一样,关键看效率高低.
PN当等的时候就比较discription,vendor,vendor_pn 等。PN和discription原则上是一一对应的,但是它们可能有许多不同的vendor.当vendor 相同的时候就接着比较别的类别,当PN的vendor 在B表中有而A表中没有的话也需要插入到C表新的一行。A表和B表的这些比较项都要插入C表,比如discription,A表的列插入C表的discription,B表的列插入C表的E_disc.然后C表的D1列用来显示两者S(ame)/D(ifferent).关系表如上图
insert into C
select a.part_number,
a.description,b.description as E_desc,(case when a.description=b.description then 'S' else 'D' end) as D1,
a.vendor,b.vendor as E_vendor,(case when a.vendor=b.vendor then 'S' else 'D' end) as D2,
a.vendor_part,b.vendor_part as E_vendor_part,(case when a.vendor_part=b.vendor_part then 'S' else 'D' end) as D3,
vendor_part_status,b.vendor_part_status as E_vendor_part_status,(case when a.vendor_part_status=b.vendor_part_status then 'S' else 'D' end) as D4,
a.restriction,b.restriction as E_restriction,(case when a.restriction=b.restriction then 'S' else 'D' end) as D5,
a.restriction_code,b.restriction_code as E_restriction_code,(case when a.restriction_code=b.restriction_code then 'S' else 'D' end) as D5
from a inner join b on a.part_number=b.part_number
另
当PN的vendor 在B表中有而A表中没有的话也需要插入到C表对应的一行。 怎么实现呢?
select part_number,'Not match' as re
from a where not exists(select 1 from b where part_number=a.part_number)
用 left join,并且对 A 表的相应列采用 isnull(a.columnname,'')
1如果有个a 中的partnumber 在b中没有,怎么将a 的对应列插入C,并在re 中写入“not match”
2 如果有个partnumber 在a 中只有一个vendor,而在b中有两个vendor,怎么实现只插入b的对应列
现在出现的将是
a a
a b
a c
b a
b b
b c
想实现的是只导出
a a
b b
X c