–Full scale update
create table pdata.dbo.customer(
name varchar(255)
,type varchar(255)
,type_no varchar(255)
,address varchar(255)
,tel varchar(255) )
insert into pdata.dbo.customer values( ‘ccc ‘, ‘sfz’,’ 1201334′,’ a1 ‘,’ 1′)
insert into pdata.dbo.customer values( ‘AAA ‘, ‘sfz’,’ 1301121′,’ a2 ‘,’ 128285 ‘)
insert into pdata.dbo.customer values( ‘ddd ‘, ‘sfz’,’ 1201335′,’ a3 ‘,’ 1268910 ‘)
insert into pdata.dbo.customer values( ‘cef ‘, ‘sfz’,’ 1201005′,’ a9 ‘,’ 1268910 ‘)
insert into pdata.dbo.customer values( ‘verg’, ‘sfz’,’ 1201255′,’ a12 ‘,’ 1268910 ‘)
insert into pdata.dbo.customer values( ‘erf ‘, ‘sfz’,’ 1201285′,’ a89 ‘,’ 1268910′)
create table sdata.dbo.customer_20180312 (
name varchar(255)
,type varchar(255)
,type_no varchar(255)
,address varchar(255)
,tel varchar(255) )
insert into sdata.dbo.customer_20180312 values( ‘cwcwie ‘, ‘sfz’,’ 1201334′,’ a75 ‘,’ 122828 ‘)
insert into sdata.dbo.customer_20180312 values( ‘AAA ‘, ‘sfz’,’ 1301121′,’ a5cio21 ‘,’ 0 ‘)
insert into sdata.dbo.customer_20180312 values( ‘ddd ‘, ‘sfz’,’ 1201335′,’ a98 ‘,’ 1268910 ‘)
insert into sdata.dbo.customer_20180312 values( ‘cef ‘, ‘sfz’,’ 1201005′,’ a0 ‘,’ 12689102 ‘)
insert into sdata.dbo.customer_20180312 values( ‘verg’, ‘sfz’,’ 1201255′,’ a12 ‘,’ 1268910 ‘)
insert into sdata.dbo.customer_20180312 values( ‘erf ‘, ‘sfz’,’ 1201285′,’ a89 ‘,’ 0′)
select * from sdata.dbo.customer_20180312
Delete incorrect data or existing data.
delete from pdata.dbo.customer
where exists(
select 1 from sdata.dbo.customer_20180312 b
where pdata.dbo.customer.name =b.name
and pdata.dbo.customer.type = b.type
and pdata.dbo.customer.type_no=b.type_no
and(
pdata.dbo.customer.address <>b.address
or pdata.dbo.customer.tel <>b.tel
)
)
select * from pdata.dbo.customer
Insert new data
insert into pdata.dbo.customer
(name
,type
,type_no
,address
,tel
)
select
a.name
,a.type
,a.type_no
,a.address
,a.tel
from sdata.dbo.customer_20180312 a
left join pdata.dbo.customer b
on a.name =b.name
and a.type = b.type
and a.type_no=b.type_no
where b.type is null
select* from pdata.dbo.customer