Is this correct - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

Is this correct

Author

Topic

Kumar_Anil
Yak Posting Veteran

68 Posts

Posted - 2008-04-24 : 16:51:36

Im new to SQL so please bear with me & help me as to why Im not getting the desired results.

I want to find the difference between two sets of tables that reside in different databases but contain the same data.
I ONLY WANT
a. records that are only in A but not in B
b. records that are only in B but not in A
______________________________________________________________________

Here is what I wrote using something that I found in this forum -

CREATE PROCEDURE RPT_DETAILS
AS
BEGIN
DECLARE @Rowcount AS INT
DECLARE @First_Name AS VARCHAR(50)
DECLARE @Last_Name AS VARCHAR(50)
DECLARE @Id AS INT

CREATE TABLE #Prowess(ID INT NOT NULL, First_Name VARCHAR(50), Last_Name VARCHAR(50))
CREATE TABLE #SDK(ID INT NOT NULL, First_Name VARCHAR(50), Last_Name VARCHAR(50))

INSERT INTO #Prowess
SELECT bb.beenumber, be.FirstName, be.LastName FROM beebusiness bb
join beeentity be on bb.beebusinessguid = bb.beebusinessguid

INSERT INTO #SDK
SELECT cast(sa_ss as INT), first_name, last_name from ml

SELECT @ROWCOUNT = MAX(ID) FROM #SDK
PRINT '------------------------------------------------------------------------------------------'
PRINT '------------------------COMPARISION REPORT Between Prowess & SDK--------------------------'
PRINT '------------------------------------------------------------------------------------------'

PRINT 'TOTAL Difference ('+ +
CAST(@ROWCOUNT AS VARCHAR(50))

WHILE @ROWCOUNT > 0
BEGIN
SELECT @First_Name = First_name, @Last_Name = Last_name, @ID = ID
FROM #Prowess WHERE ID = @ROWCOUNT
PRINT ' * '+@First_Name+@Last_Name
SET @ROWCOUNT = @ROWCOUNT - 1
END

SELECT @ROWCOUNT = MAX(ID) FROM #Sdk

PRINT 'TOTAL Difference ('+ + CAST(@ROWCOUNT AS VARCHAR(50))

WHILE @ROWCOUNT > 0
BEGIN
SELECT @First_Name = First_name, @Last_Name = Last_name, @ID = ID
FROM #Sdk WHERE ID = @ROWCOUNT
PRINT ' * '+@First_Name+@Last_Name
SET @ROWCOUNT = @ROWCOUNT - 1
END

DROP TABLE #Prowess
DROP TABLE #Sdk

END

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-24 : 16:56:33

What is the common denominator?
How can you tell if a record that exists in Table A also exists in Table B?

E 12°55'05.25"
N 56°04'39.16"

Kumar_Anil
Yak Posting Veteran

68 Posts

Posted - 2008-04-24 : 17:01:11

It is beenumber in #Prowess side and sa_ss in #Sdk side.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-24 : 17:09:53

SELECT theIdentifier
FROM (
SELECT BeeNumber AS theIdentifier FROM #Prowess

UNION ALL

SELECT Sa_Ss FROM #Sdk
) AS d
GROUP BY theIdentifier
HAVING COUNT(*) = 1

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-25 : 02:10:59

SELECT
FROM(
SELECT bb.beenumber AS id, be.FirstName, be.LastName FROM beebusiness bb
join beeentity be on bb.beebusinessguid = bb.beebusinessguid
)t1
LEFT JOIN (
SELECT cast(sa_ss as INT) AS id, first_name, last_name from ml
)t2
ON t2.id = t1.id
WHERE t2.id IS NULL

gives records in first query (your #Prowess table)which is not in second query( your #SDK table)

b.

SELECT
FROM(
SELECT cast(sa_ss as INT) AS id, first_name, last_name from ml
)t1
LEFT JOIN (
SELECT bb.beenumber AS id, be.FirstName, be.LastName FROM beebusiness bb
join beeentity be on bb.beebusinessguid = bb.beebusinessguid
)t2
ON t2.id = t1.id
WHERE t2.id IS NULL

gives records in first query (your #SDKtable)which is not in second query( your #Prowess table)

Peso has given same solution but using GROUP BY approach

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-25 : 03:04:56

ANd if you want a normailized resultset including which table that the ID belings to,

SELECT		MAX(theTableName) AS theTableName,
		theIdentifier
FROM		(
			SELECT	'#Prowess' AS theTableName,
				BeeNumber AS theIdentifier
			FROM	#Prowess

			UNION ALL

			SELECT	'#Sdk',
				Sa_Ss
			FROM	#Sdk
		) AS d
GROUP BY	theIdentifier
HAVING		COUNT(*) = 1

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources