Diffrence between 2 tables

Question

I have two tables which is one of them is AI: Ascent insensitive and other is AS: Ascent sensitive

How can I compare between them and take difference between them? I used except

select  ID,FIRST,FATHER,GRAND  from t1

EXCEPT

select ID,FIRST,FATHER,GRAND  from t2

But I got this error:

Cannot resolve the collation conflict between "Arabic_CI_AS" and "Arabic_100_CI_AI" in the EXCEPT operation

you have to "convert" the collation of one of the tables in the select statement using collation <collation name> after each selected column — R Yoda
– R Yoda, Commented Jan 28, 2018 at 11:31
To find collation you can use SELECT *, COLLATION_NAME FROM SYS.COLUMNS WHERE OBJECT_ID IN ( SELECT OBJECT_ID FROM SYS.OBJECTS WHERE TYPE='U' AND NAME='T1' ) — PSK
– PSK, Commented Jan 28, 2018 at 11:34

R Yoda · Accepted Answer · 2018-01-28 11:34:38Z

1

Choose an appropriate collation so that the comparison of the strings does match as wanted (e. G. "CI" = case sensitive/insensitive), the use your collation like this (ignoring the problem of of the ID column in EXCEPT:

select  ID,
        FIRST  collate Arabic_CI_AS,
        FATHER collate Arabic_CI_AS,
        GRAND  collate Arabic_CI_AS
from t1

EXCEPT

select ID,
       FIRST  collate Arabic_CI_AS,
       FATHER collate Arabic_CI_AS,
       GRAND  collate Arabic_CI_AS
from t2

answered Jan 28, 2018 at 11:34

R Yoda

8,8502 gold badges55 silver badges99 bronze badges

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

1 Comment

Baban Jamal Over a year ago

THANKS SO MUCH IT WORKED GREAT

Mizanur Rahman · Accepted Answer · 2018-01-28 11:39:02Z

0

it working here

USE [TestDb] GO

Table 1

CREATE TABLE [dbo].[t1](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [FIRST] [nchar](10) NULL,
    [FATHER] [nchar](10) NULL,
    [GRAND] [nchar](10) NULL,
 CONSTRAINT [PK_t1] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Table 2

CREATE TABLE [dbo].[t2](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [FIRST] [nchar](10) NULL,
    [FATHER] [nchar](10) NULL,
    [GRAND] [nchar](10) NULL,
 CONSTRAINT [PK_t2] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

answered Jan 28, 2018 at 11:39

Mizanur Rahman

2722 silver badges11 bronze badges

1 Comment

R Yoda Over a year ago

I am not sure if the test works in your case because of the newly created tables are living in the same database. Normally you have to specify a different than the DB default collation explicitly when you create a table (which I cannot see in your scripts above and could mean that both tables use the same collation): learn.microsoft.com/en-us/sql/relational-databases/collations/…

Collectives™ on Stack Overflow

Diffrence between 2 tables

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related