Comparing duplicate records in two tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Comparing duplicate records in two tables

Author

Topic

Topaz
Posting Yak Master

199 Posts

Posted - 2010-11-18 : 10:27:28

I am trying to import some data from one table (NWsept) to another table (wce_contact).

The table that I am trying to import into (wce_contact) won't allow duplicate values in the 'uniqueid' field.

How can I write a query that would get SQL to check the uniqueid of NWsept with the wce_contact uniqueid and display the offending records.

I'm really stuck...

JT

X002548
Not Just a Number

15586 Posts

Posted - 2010-11-18 : 10:43:29

it would be a big help if you posted the DDL with constraints

but it would be like

FROM Table1 JOIN Table2 ON Table1.key = Table2.Key

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Topaz
Posting Yak Master

199 Posts

Posted - 2010-11-18 : 10:51:24

The DDL constraints are exactly the same for each table.

Uniqueid = Varchar

JT

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-11-18 : 11:08:37

in case NWSept has duplicate for a value of uniqueid, how do you determine which value you want?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2010-11-18 : 11:08:44

for us to write the code, we need the DDL with the constraints of the table

Read the hint link in my sig

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Topaz
Posting Yak Master

199 Posts

Posted - 2010-11-18 : 11:21:10

CREATE TABLE [dbo].[NWsept](
[uniqueid] [varchar](16) COLLATE Latin1_General_CI_AS NULL,
[website] [varchar](75) COLLATE Latin1_General_CI_AS NULL,
[expressemail] [varchar](254) COLLATE Latin1_General_CI_AS NULL,
[blank] [varchar](50) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]

The other table wce_Contact has far too many fields to post on here. But their data type and character length are exactly the same

JT

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-11-18 : 11:23:53

quote:
Originally posted by Topaz

CREATE TABLE [dbo].[NWsept](
[uniqueid] [varchar](16) COLLATE Latin1_General_CI_AS NULL,
[website] [varchar](75) COLLATE Latin1_General_CI_AS NULL,
[expressemail] [varchar](254) COLLATE Latin1_General_CI_AS NULL,
[blank] [varchar](50) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]

The other table wce_Contact has far too many fields to post on here. But their data type and character length are exactly the same

JT

you didnt answer me yet

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Topaz
Posting Yak Master

199 Posts

Posted - 2010-11-18 : 11:26:56

quote:
Originally posted by visakh16

in case NWSept has duplicate for a value of uniqueid, how do you determine which value you want?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Well, sorry for neglecting your reply.

I just want to see all duplicate values from either table.

JT

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-11-18 : 11:34:58

but how do you insert duplicate values to table2? it has a unique constraint isnt it? then which one you would choose for its insertion is my question.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Topaz
Posting Yak Master

199 Posts

Posted - 2010-11-18 : 11:45:07

Ok, I think what you are asking me is making sense?

If it tries to insert a duplicare record, I don't want to choose any table to insert the data into. I simply don't want it to be imported.

JT

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-11-18 : 11:49:13

what according to you represents a duplicate record of a uniqueid? based on what other column value(s) you determine its to be neglected?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2010-11-18 : 12:02:30

what you can't cut and paste?

And you left out the primary key

And let me restate

You want to see where the keys are equal between the 2 tables?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Topaz
Posting Yak Master

199 Posts

Posted - 2010-11-19 : 07:57:26

I answered my own question with this query. Thanks for your efforts

SELECT
[UNIQUEID]
,[website]
,[expressemail]
,[blank]
FROM [wce_site].[dbo].[NWsept] AS src
WHERE NOT EXISTS (SELECT * FROM [wce_site].[dbo].[wce_contact] AS tgt WHERE tgt.uniqueid= src.uniqueid)

JT

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2010-11-19 : 08:47:00

so in effect the original questions can be re-worded.

"how do i insert into table#1 from table#2 and check if any of the table#2 primarykey values are already in use in table#1"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources