April 25, 2009 at 8:56 am
Hi All,
I have two different tables with duplicate rows and non-duplicate rows. I have to analyze the data and make sure the date is correct b/w two DB. I just what to compare and I don't want to use cursor because it takes time.
Is there any better way to compare and I don't want to delete the duplicates?
Guys please help I appreciate your help.
Thanks in advance.
April 25, 2009 at 9:08 am
Hi
You can use a partitioned row_number over the field(s) which define the "uniqueness":
DECLARE @t TABLE (NonUniqueId INT)
INSERT INTO @t
SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 2
UNION ALL SELECT 1
UNION ALL SELECT 3
; WITH todo (RowNumber) AS
(
SELECT
ROW_NUMBER() OVER (PARTITION BY NonUniqueId ORDER BY NonUniqueId)
FROM @t
)
DELETE FROM todo WHERE RowNumber != 1
SELECT * FROM @t
Greets
Flo
April 25, 2009 at 9:13 am
Thanks a lot for a very quick reply. If I have any questions I will let you know.
Thanks again
April 25, 2009 at 9:28 am
I'm glad that I could help!
Best wishes
Flo
April 25, 2009 at 11:20 am
The EXCEPT and INTERSECT operators can also help you compare data between two different queries. They provide a pretty easy way to compare data.
April 25, 2009 at 10:14 pm
I tried except and intersect. I have a very large data base the query runs very slow. Actully I will put my question in a diffrent way.
I have a table1 ........
Name Address
x xyz
y 123
z abc
I have an another table2
Name1 Address2
x xyz
a 456
b hhh
and I want a result some thing ......... like this
Name Address Name1 Address1
x xyz x xyz
y 123 Blank Blank
z abc Blank Blank
Blank Blank a 456
Blank Blank b hhh
So that I can show what exactly is missing.............
Thanks a lot in advance.
April 25, 2009 at 10:33 pm
Based off your example you could run a query like this. However, this would pull back all the data from both tables and as you said the data is large and this could take quite a while. You should at least add the where clause that is commented out in the example to only limit the rows with no matches in both tables.
Declare @table1 Table (Name varchar(10), Address varchar(10))
Declare @table2 Table (Name2 varchar(10), Address2 varchar(10))
Insert Into @table1
Select 'x', 'xyz' UNION ALL
Select 'y', '123' UNION ALL
Select 'z', 'abc'
Insert Into @table2
Select 'x', 'xyz' UNION ALL
Select 'a', '456' UNION ALL
Select 'b', 'hhh'
Select *
From @table1 A FULL OUTER JOIN
@table2 B ON
A.Address = B.Address2 and
A.Name = B.Name2
--Where A.Name IS NULL OR B.Name2 IS NULL
April 26, 2009 at 2:41 pm
Thanks a lot
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy