January 24, 2013 at 11:37 am
Hi,
I want to find out a way to extract the different or non-matching rows from two similar tables- the tables have identical columns but may have different rows, the top 11 rows of the tables are shown below:
Table #1
iNumeratoriDenominatorvShortNameVORGCODE
47138780484864 Q1 HONDA
39180100471756 Q10 HONDA
43826500483794 Q11 HONDA
45461740475420 Q2 HONDA
45808440484209 Q3 HONDA
47439600480386 Q4 HONDA
47740480484768 Q5 HONDA
47898900484323 Q6 HONDA
47459060484057 Q7 HONDA
46835980484574 Q8 HONDA
46774700481268 Q9 HONDA
Table # 2
iNumeratoriDenominatorvShortNameVORGCODE
230540025344 COPQ7 HONDA
63185740649770 Q1 HONDA
52054900632201 Q10 HONDA
58769900648375 Q11 HONDA
60982800637487 Q2 HONDA
61411780648833 Q3 HONDA
63510000643797 Q4 HONDA
63972600649580 Q5 HONDA
64169020648859 Q6 HONDA
63604000648628 Q7 HONDA
62772620649334 Q8 HONDA
62699080645061 Q9 HONDA
I want a query that will give me the different rows with table name, so for example the last row of table does not exist in table one
the result should be
table # 2
62699080645061 Q9 HONDA
Please reply asap, thanks a lot for helping!
January 24, 2013 at 12:30 pm
Can you post ddl (create table scripts) and sample data (insert statements)?
I don't understand why the output would be only the one row. None of the rows you posted are in table1. Seems a little clarification would help greatly.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 24, 2013 at 12:35 pm
My bad, you are right all the rows are different, here is a better data for comparison:
Table1
iNumeratoriDenominatorvShortNameVORGCODE
34146680352107Q1HONDA
28364700343188Q10HONDA
31487900351551Q11HONDA
32852820344581Q2HONDA
33225000351591Q3HONDA
34326200348397Q4HONDA
34644640352002Q5HONDA
34760860351736Q6HONDA
34400420351338Q7HONDA
33943420351899Q8HONDA
33882920349562Q9HONDA
Table2
iNumeratoriDenominatorvShortNameVORGCODE
34146680352107Q1HONDA
28364700343188Q10HONDA
31487900351551Q11HONDA
32852820344581Q2HONDA
33225000351591Q3HONDA
34326200348397Q4HONDA
34644640352002Q5HONDA
34760860351736Q6HONDA
34400420351338Q7HONDA
33943420351899Q8HONDA
43882920349562Q9HONDA
Now the last row is different which would be returned as a result.
43882920349562Q9HONDA
Basically i have more than 200,000 records - table structure is same for both.
January 24, 2013 at 12:46 pm
Can you post ddl (create table scripts) and sample data (insert statements)? Something so we can create tables and data locally?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 24, 2013 at 12:49 pm
create script:
CREATE TABLE [dbo].[tablep1](
[iNumerator] [float] NULL,
[iDenominator] [float] NULL,
[vShortName] [nvarchar](255) NULL,
[VORGCODE] [nvarchar](255) NULL
) ON [PRIMARY]
Insert script:
INSERT INTO [comparison].[dbo].[tablep1]
([iNumerator]
,[iDenominator]
,[vShortName]
,[VORGCODE])
VALUES
(<iNumerator, float,>
,<iDenominator, float,>
,<vShortName, nvarchar(255),>
,<VORGCODE, nvarchar(255),>)
GO
January 24, 2013 at 12:52 pm
This should be reasonably close...not sure what columns you are using to determine if it exists.
select iNumerator, iDenominator, vShortName, VORGCODE
from Table2
where NOT EXISTS
(
select iNumerator, iDenominator, vShortName, VORGCODE
from Table1
Where Table1.iNumerator = Table2.iNumerator
and Table1.iDenominator = Table2.iDenominator
and Table1.vShortName = Table2.vShortName
and Table1.VORGCODE = Table2.VORGCODE
)
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 24, 2013 at 12:54 pm
wmalik001 (1/24/2013)
create script:CREATE TABLE [dbo].[tablep1](
[iNumerator] [float] NULL,
[iDenominator] [float] NULL,
[vShortName] [nvarchar](255) NULL,
[VORGCODE] [nvarchar](255) NULL
) ON [PRIMARY]
This will work but do you really have no primary key?
Insert script:
INSERT INTO [comparison].[dbo].[tablep1]
([iNumerator]
,[iDenominator]
,[vShortName]
,[VORGCODE])
VALUES
(<iNumerator, float,>
,<iDenominator, float,>
,<vShortName, nvarchar(255),>
,<VORGCODE, nvarchar(255),>)
GO
This doesn't really help. It need to be a usable script that I can copy and paste into SSMS. This has no data.
You should probably take a look at the first link in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 24, 2013 at 1:00 pm
First of, i am sorry for not providing the information in correct format secondly thanks a lot for your replies.
I will provide the ddl soon.
January 24, 2013 at 1:15 pm
wmalik001 (1/24/2013)
First of, i am sorry for not providing the information in correct format secondly thanks a lot for your replies.I will provide the ddl soon.
No problem. It is hard to know how to post this type of stuff if you have never done it before. 🙂
Pretty sure that the sample code I posted is going to be close to what you want.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 24, 2013 at 3:47 pm
How about EXCEPT?
SELECT 'TableA', *
FROM (
SELECT * FROM TableA
EXCEPT
SELECT * FROM TableB
) a
UNION ALL
SELECT 'TableB', *
FROM (
SELECT * FROM TableB
EXCEPT
SELECT * FROM TableA
) a
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
January 24, 2013 at 5:02 pm
ChrisM@home (1/24/2013)
How about EXCEPT?
+1
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 24, 2013 at 8:29 pm
Something like this might work too:
SELECT [Table]=CASE WHEN a.[iNumerator] IS NULL THEN 'Table2' ELSE 'Table1' END
,[iNumerator]=CASE WHEN a.[iNumerator] IS NULL THEN b.[iNumerator] ELSE a.[iNumerator] END
,[iDenominator]=CASE WHEN a.[iNumerator] IS NULL THEN b.[iDenominator] ELSE a.[iDenominator] END
,[vShortName]=CASE WHEN a.[iNumerator] IS NULL THEN b.[vShortName] ELSE a.[vShortName] END
,[VORGCODE]=CASE WHEN a.[iNumerator] IS NULL THEN b.[VORGCODE] ELSE a.[VORGCODE] END
FROM Table1 a
FULL JOIN Table2 b
ON a.[iNumerator] = b.[iNumerator] AND a.[iDenominator] = b.[iDenominator] AND
a.[vShortName] = b.[vShortName] AND a.[VORGCODE] = b.[VORGCODE]
WHERE a.[iNumerator] IS NULL OR b.[iNumerator] IS NULL
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 24, 2013 at 11:49 pm
I figured out this and it seems to be working:
SELECT MIN(vorgcode) as vorgcode, inumerator, idenominator, vshortname
FROM
(
SELECT 'wb_1' as Tabletest, inumerator, idenominator, vshortname,vorgcode
FROM wb_1
UNION ALL
SELECT 'wb_2' as Tabletest, inumerator, idenominator, vshortname,vorgcode
FROM wb_2
) tmp
GROUP BY inumerator, idenominator, vshortname
HAVING COUNT(*) = 1
ORDER BY vshortname
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply