sql server - SQL query issue in avoid duplicates in INSERT INTO SELECT? -
the following query works perfectly,
insert [egallery].dbo.[customerdetails] select distinct b.countyb 'mobile' , cast(buildingb varchar(100)) 'email' , a.cardcode , a.cardname 'first name' , '' 'last name' , '' gender , cast(a.address varchar(1000)) 'address' , convert(varchar(10), a.u_birthday,105) 'birthday' , convert(varchar(10), a.u_annivday ,105) 'anniversary' , case when a.cardcode '%%'+ c.whscode +'%%' convert(varchar(10) , a.docdate ,105) else convert(varchar(10), (select x.createdate ocrd x x.cardcode = a.cardcode) ,105) end 'joindate' , c.whscode 'joinstore','open' status ,(select getdate()) createddatetime,(select getdate()) processdatetime, '' statusmsg oinv inner join inv12 b on a.docentry = b.docentry inner join inv1 c on a.docentry = c.docentry c.linenum = '0' --b.countyb not in(select d.mobile [egallery].dbo.[customerdetails] d d.mobile=b.countyb) --not exists (select mobile [egallery].dbo.[customerdetails] d d.mobile=b.countyb)
but before insert records [egallery].dbo.[customerdetails]
table, need check whether phone number exists in table. if record exists, there no need insert again. have added 1 more condition (which have commented out in query) reports error while running query:
cannot resolve collation conflict between "sql_latin1_general_cp850_ci_as" , "latin1_general_ci_ai" in equal operation.
according here have add collate database_default
queries this:
where c.linenum = '0' , b.countyb not in(select d.mobile [egallery].dbo.[customerdetails] d d.mobile collate database_default = b.countyb collate database_default) , not exists (select mobile [egallery].dbo.[customerdetails] d d.mobile collate database_default = b.countyb collate database_default)
Comments
Post a Comment