SQL query issue in avoid duplicates in INSERT INTO SELECT?

Question

The following query works perfectly,

insert into [EGallery].dbo.[CustomerDetails] 
Select Distinct B.CountyB as 'Mobile' ,  Cast(BuildingB as Varchar(100)) as 'Email' , 
A.CardCode , A.CardName as 'First Name' , '' as 'Last Name' , 
'' as Gender , Cast(A.Address as Varchar(1000)) as 'Address' , Convert(Varchar(10), A.U_BirthDay,105) as 'birthday' , 
Convert(Varchar(10), A.U_AnnivDay ,105) as 'Anniversary' , 
Case 
When A.CardCode Like '%%'+ C.WhsCode +'%%'  Then Convert(Varchar(10) , A.DocDate ,105) 
Else Convert(Varchar(10), (Select X.CreateDate From OCRD X Where X.CardCode = A.CardCode) ,105) End as 'JoinDate' , 
C.WhsCode as 'JoinStore','Open' as Status ,(Select GETDATE()) as CreatedDateTime,(Select GETDATE()) as ProcessDateTime, '' as StatusMSg 
From OINV A 
Inner Join INV12 B On A.DocEntry = B.DocEntry 
Inner Join INV1 C On A.DocEntry = C.DocEntry 
Where C.LineNum = '0'

--B.CountyB not in(select D.Mobile from [EGallery].dbo.[CustomerDetails] D where D.Mobile=B.CountyB)
--not exists (select Mobile from [EGallery].dbo.[CustomerDetails] D where D.Mobile=B.CountyB)

But before I insert records into the [EGallery].dbo.[CustomerDetails] table, I need to check whether the phone number already exists in the table. If the record already exists, there is no need to insert it again. For that I have added one more condition (which I have commented out in the query) but it reports this error while running the query:

Cannot resolve the collation conflict between "SQL_Latin1_General_CP850_CI_AS" and "Latin1_General_CI_AI" in the equal to operation.

Error : Cannot resolve the collation conflict between "SQL_Latin1_General_CP850_CI_AS" and "Latin1_General_CI_AI" in the equal to operation. — Vengat
– Vengat, Commented Jul 28, 2017 at 9:01

Evgeny · Accepted Answer · 2017-07-28 09:18:08Z

1

Try to do this before your query:

USE [db name for object INV12]
GO

ALTER TABLE [EGallery].dbo.[CustomerDetails]
  ALTER COLUMN Mobile
    VARCHAR(100) COLLATE Latin1_General_CI_AS NOT NULL

ALTER TABLE INV12
  ALTER COLUMN CountyB
    VARCHAR(100) COLLATE Latin1_General_CI_AS NOT NULL

UPDATE1: If you have an index on one of this columns, or on both of them, you need to delete it and create index again after a new collation will be changed.

answered Jul 28, 2017 at 9:18

Evgeny

6372 gold badges9 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Looki · Accepted Answer · 2017-07-28 09:08:41Z

0

According to here you have to add COLLATE DATABASE_DEFAULT to the queries like this:

Where C.LineNum = '0' AND
B.CountyB not in(select D.Mobile from [EGallery].dbo.[CustomerDetails] D where D.Mobile COLLATE DATABASE_DEFAULT = B.CountyB COLLATE DATABASE_DEFAULT) AND
not exists (select Mobile from [EGallery].dbo.[CustomerDetails] D where D.Mobile COLLATE DATABASE_DEFAULT = B.CountyB COLLATE DATABASE_DEFAULT)

edited Jul 28, 2017 at 9:08

answered Jul 28, 2017 at 8:16

Looki

9621 gold badge6 silver badges15 bronze badges

1 Comment

Vengat Over a year ago

yes, Error i have faced "Cannot resolve the collation conflict between "SQL_Latin1_General_CP850_CI_AS" and "Latin1_General_CI_AI" in the equal to operation."

René Hoffmann · Accepted Answer · 2017-07-28 08:27:50Z

0

I recommend you to use a MERGE statement, as it

performs insert, update, or delete operations on a target table based on the results of a join with a source table. For example, you can synchronize two tables by inserting, updating, or deleting rows in one table based on differences found in the other table.

answered Jul 28, 2017 at 8:27

René Hoffmann

2,8252 gold badges23 silver badges45 bronze badges

Collectives™ on Stack Overflow

SQL query issue in avoid duplicates in INSERT INTO SELECT?

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related