• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 185
  • Last Modified:

Query to find duplicate key values between two tables

I'm copying data from my primary table to my archive table as part of an automated process.  This was working but now the archive tables has got a bunch of key field values that duplicate what is in my main table.  I need a query to compare these tables and find key values that are duplicated between tables.  Then to change the archive table to an unused number.  I can manually manually fix the problem by adding a bunch of 1's to the number.  For example:
  Changing  Archive_Issued_Traveler_Steps.Issued_Traveler_Steps_ID from 776182 to 1111776182
I'm looking for a query that could do that.
This is a simple query of the archive table and the field that I'm interested in:
SELECT Archive_Issued_Traveler_Steps.Issued_Traveler_Steps_ID
FROM Archive_Issued_Traveler_Steps;

This is the main table and the field to compare to the archive table
SELECT Issued_Traveler_Steps.Issued_Traveler_Steps_ID
FROM Issued_Traveler_Steps;

Open in new window

0
schmir1
Asked:
schmir1
  • 11
  • 7
  • 7
  • +2
2 Solutions
 
sameer2010Commented:
Use this code..and substitute the column names as applicable
select case 
when t1.Issued_Traveler_Steps_ID is null then t2.Issued_Traveler_Steps_ID
else
111*power(10,len(t2.Issued_Traveler_Steps_ID))+t2.Issued_Traveler_Steps_ID
end as Issued_Traveler_Steps_ID1,
t2.column2, t2.column3
from Issued_Traveler_Steps as t2
left outer join Archive_Issued_Traveler_Steps as t1
on t2.Issued_Traveler_Steps_ID = t1.Issued_Traveler_Steps_ID 

Open in new window

0
 
sameer2010Commented:
I have assumed 3 1s to be added (111) and the type of ID column as integer. If it is string, you can replace the logic to append 1s with concatenation of 111 string.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
js-profiCommented:
Better change primary key of archive table to a sequence number (automatically). The old primary key should be turned to a normal (indexed) data type.
0
 
SharathData EngineerCommented:
>>  I need a query to compare these tables and find key values that are duplicated between tables.

If I understand correctly, you will be having duplicate values in archive table only. i.e. for a value 1234, there is one record in your main table and duplicate records in your archive table. How exactly you want your output? To retrieve duplicate key values from archive table, you can try like this.

SELECT Issued_Traveler_Steps_ID
FROM Archive_Issued_Traveler_Steps
GROUP BY Issued_Traveler_Steps_ID
HAVING COUNT(Issued_Traveler_Steps_ID) > 1;

Once you get the duplicate Issued_Traveler_Steps_ID values, you can do whatever you want.

Let us know what exactly you are looking for.
0
 
schmir1Author Commented:
sameer2010:
  Your query looks interesting.  I didn't know there were Case statements in queries.  Anyhow, I don't understand it very well.  Can you help me.  

Is t2 the table to compare to t1 with t2 being the table to change?   Also, I only want to change column 1?  I want to change Archive_Traveler_Steps table.  Does the query below look right?  Also, how would I modify it so that it would show the dups without changes anything (for testing).

Thanks
select case  
when Issued_Traveler_Steps.Issued_Traveler_Steps_ID is null then Archive_Issued_Traveler_Steps.Issued_Traveler_Steps_ID 
else 
111*power(10,len(Archive_Issued_Traveler_Steps.Issued_Traveler_Steps_ID))+Archive_Issued_Traveler_Steps.Issued_Traveler_Steps_ID 
end as Issued_Traveler_Steps_ID1
from Issued_Traveler_Steps as Archive_Issued_Traveler_Steps 
left outer join Archive_Issued_Traveler_Steps as Issued_Traveler_Steps 
on Archive_Issued_Traveler_Steps.Issued_Traveler_Steps_ID = Issued_Traveler_Steps.Issued_Traveler_Steps_ID 

Open in new window

0
 
sameer2010Commented:
Hi,

You need not change anything in the query that I wrote above. I assumed Issued_Traveler_Steps as T2 and Archive_Issued_Traveler_Steps as T1. T2 being the table from where the data needs to be inserted.
This is just a SELECT query, so it would show you the data without any modifications. If you want to actually modify the table, then you would have to user INSERT INTO TABLE_NAME(above query).
0
 
schmir1Author Commented:
I got the following error when I ran your query:

Msg 208, Level 16, State 1, Line 1
Invalid object name 'Issued_Traveler_Steps'.
select case  
when t1.Issued_Traveler_Steps_ID is null then t2.Issued_Traveler_Steps_ID 
else 
111*power(10,len(t2.Issued_Traveler_Steps_ID))+t2.Issued_Traveler_Steps_ID 
end as Issued_Traveler_Steps_ID1, 
t2.Step_Num, t2.Build_Step 
from Issued_Traveler_Steps as t2 
left outer join Archive_Issued_Traveler_Steps as t1 
on t2.Issued_Traveler_Steps_ID = t1.Issued_Traveler_Steps_ID 

Open in new window

0
 
SharathData EngineerCommented:
Is Issued_Traveler_Steps the correct table name?
0
 
schmir1Author Commented:
Here are the two names copied from the server.  I am using Management Studio to run the query.  The tables have "dbo." in front of them but I don't think that has to be added.

Issued_Traveler_Steps
Archive_Issued_Traveler_Steps
0
 
SharathData EngineerCommented:
Are you running the query in correct database?

Try like this.
use YourDataBaseName
select case  
when t1.Issued_Traveler_Steps_ID is null then t2.Issued_Traveler_Steps_ID 
else 
111*power(10,len(t2.Issued_Traveler_Steps_ID))+t2.Issued_Traveler_Steps_ID 
end as Issued_Traveler_Steps_ID1, 
t2.Step_Num, t2.Build_Step 
from Issued_Traveler_Steps as t2 
left outer join Archive_Issued_Traveler_Steps as t1 
on t2.Issued_Traveler_Steps_ID = t1.Issued_Traveler_Steps_ID

Open in new window

0
 
schmir1Author Commented:
My bad.  I'm not that familiar with SQL Server stuff.  

I got the output now but it looks like it is listing all the records with Issued_Traveler_Steps_ID that occur in Issued_Traveler_Steps table but do not occur in Archive_Issued_Traveler_Steps.?  I need it to list all the records from Archive_Issued_Traveler_Steps that have Issued_Traveler_Steps_ID values that are identicial in both Issued_Traveler_Steps and Archive_Issued_Traveler_Steps
0
 
SharathData EngineerCommented:
are you looking for this?
use YourDataBaseName
select case  
when t1.Issued_Traveler_Steps_ID is null then t2.Issued_Traveler_Steps_ID 
else 
111*power(10,len(t2.Issued_Traveler_Steps_ID))+t2.Issued_Traveler_Steps_ID 
end as Issued_Traveler_Steps_ID1, 
t2.Step_Num, t2.Build_Step 
from Issued_Traveler_Steps as t2 
inner join Archive_Issued_Traveler_Steps as t1 
on t2.Issued_Traveler_Steps_ID = t1.Issued_Traveler_Steps_ID

Open in new window

0
 
schmir1Author Commented:
I still wasn't getting the right values so I changed the query to the following.  It correctly labels the records that "Match" and that "No Match".  So it's getting closer.
select 
case  
  when t1.Issued_Traveler_Steps_ID = t2.Issued_Traveler_Steps_ID
  then 'match' 
  else 'no match'
end as Issued_Traveler_Steps_ID1, 
t2.Issued_Traveler_Steps_ID, t2.Issued_Traveler_ID, t2.Step_Num, t2.Build_Step from Archive_Issued_Traveler_Steps as t2 
left outer join Issued_Traveler_Steps as t1 
on t2.Issued_Traveler_Steps_ID = t1.Issued_Traveler_Steps_ID 

Open in new window

0
 
SharathData EngineerCommented:
>> I still wasn't getting the right values so I changed the query to the following.  It correctly labels the records that "Match" and that "No Match".  So it's getting closer.

Do you still looking for assistance?
0
 
schmir1Author Commented:
Yes.  I would like to be able to list only those that match.  Then when that looks right I would like to change the values.
0
 
schmir1Author Commented:
Also, when I had this line in I was getting overflow errors:

111*power(10,len(t2.Issued_Traveler_Steps_ID))+t2.Issued_Traveler_Steps_ID  
0
 
sameer2010Commented:
What is the definition of Issued_Traveler_Steps_ID?
0
 
sameer2010Commented:
Remove multiple occurrences of 111 to 11 and see if that helps. But if this is happening, it means your idea of prefixing all the IDs with 111 will not work because IDs are already such in length that more number could possibly not be prefixed.
0
 
schmir1Author Commented:
It's a 4 byte integer.

If we could get the first part working (listing only the matching records) for now that would be great.
0
 
sameer2010Commented:
This should work. It would return all the matching IDs only.
select 
t2.Issued_Traveler_Steps_ID, t2.Issued_Traveler_ID, t2.Step_Num, t2.Build_Step from Archive_Issued_Traveler_Steps as t2 
inner join Issued_Traveler_Steps as t1 
on t2.Issued_Traveler_Steps_ID = t1.Issued_Traveler_Steps_ID 

Open in new window

0
 
schmir1Author Commented:
That's a winner.  Now how would I update them?  The highest number is 776168 so adding 111 should be OK?
0
 
SharathData EngineerCommented:
try this query. you need to alter Issued_Traveler_Steps_ID to bigint to store these big values.

update t2
   set Issued_Traveler_Steps_ID = convert(bigint,'1111'+convert(varchar,Issued_Traveler_Steps_ID))
  from Archive_Issued_Traveler_Steps t2
  join Issued_Traveler_Steps t1
    on 2.Issued_Traveler_Steps_ID = t1.Issued_Traveler_Steps_ID

0
 
SharathData EngineerCommented:
To alter the column, try like this.

ALTER  Archive_Issued_Traveler_Steps ALTER  Issued_Traveler_Steps_ID BIGINT

http://technet.microsoft.com/en-us/library/ms190273.aspx
0
 
sameer2010Commented:
Try this:
update Archive_Issued_Traveler_Steps
set Issued_Traveler_Steps_ID = (
select 
111*power(10,len(t2.Issued_Traveler_Steps_ID))+t2.Issued_Traveler_Steps_ID from Archive_Issued_Traveler_Steps as t2 
where
t2.Issued_Traveler_Steps_ID = t1.Issued_Traveler_Steps_ID)

Open in new window

0
 
schmir1Author Commented:
I used Sharath's query and it worked fine.  Thanks to both of you for your help
0
 
schmir1Author Commented:
 Excellent answers.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 11
  • 7
  • 7
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now