[help] Possible to script a diff into SQL Query?

My goal is to add a diff into a query that grabs data from 2 different tables.

The code:
SELECT
MIN(TableName) as TableName,
ID1, COL1, COL2, COL3, COL4, COL5, COL6, COL7, COL8
,COL9, COL10, COL11, COL12, COL13, COL14, COL15, --COL16,
COL17, COL18, COL19, COL20, COL21
FROM
(
SELECT 'Table A' as TableName,
SessionID as ID1,
StartDateCode as COL1,
StartTimeCode as COL2,
EndDateCode as COL3,
EndTimeCode as COL4,
HandledByCode as COL5,
DispositionCode as COL6,
DNISCode as COL7,
CallServiceQueueCode as COL8,
ApplicationCode as COL9,
IVREndPointCode as COL10,
BankCode as COL11,
TotalQueueTimeSS as COL12,
TotalAgentTalkTimeSS as COL13,
TotalAgentHoldTimeSS as COL14,
TotalAgentHandleTimeSS as COL15,
--TotalIVRTimeSS as COL16,
AfterHoursFlag as COL17,
SourceSystemID as COL18,
anubisTransferExtNumber as COL19,
anubisEndPoint as COL20,
AccountNumber as COL21

from [pdx0sql45].Rubicon_Marts.dbo.INB_Call_Fact
where startdatecode between 2738 and 2769

UNION all

SELECT 'Table B' as TableName,
SessionID as ID1,
StartDateCode as COL1,
StartTimeCode as COL2,
EndDateCode as COL3,
EndTimeCode as COL4,
HandledByCode as COL5,
DispositionCode as COL6,
DNISCode as COL7,
CallServiceQueueCode as COL8,
ApplicationCode as COL9,
IVREndPointCode as COL10,
BankCode as COL11,
TotalQueueTimeSS as COL12,
TotalAgentTalkTimeSS as COL13,
TotalAgentHoldTimeSS as COL14,
TotalAgentHandleTimeSS as COL15,
--TotalIVRTimeSS as COL16,
AfterHoursFlag as COL17,
SourceSystemID as COL18,
anubisTransferExtNumber as COL19,
anubisEndPoint as COL20,
AccountNumber as COL21

from pdx0sql04.Rubicon_Marts.dbo.INB_Call_Fact
where startdatecode between 2738 and 2769
) tmp

GROUP BY ID1, COL1, COL2, COL3, COL4, COL5, COL6, COL7, COL8
,COL9, COL10, COL11, COL12, COL13, COL14, COL15, --COL16,
COL17, COL18, COL19, COL20, COL21
HAVING COUNT(*) = 1
ORDER BY 2,1

Is it possible to add a command into the query to output diff/compare scenario?

Thanks in advance for any help.

If you are using SQL Server 2005 you might be able to use the EXCEPT operator instead of the UNION ALL operator. Give a look to the EXCEPT operator in books online.

If the EXCEPT operator looks like it does what you want and it does not perform to your satisfaction, come back and talk to us again. While the EXCEPT operator might be conceptually the most straight forward approach it is not always the most efficient.

Kent

|||Yes, we're using 2005. I'll look into using the EXCEPT op. thanks for the advise, Kent.

[database_name . [schema_name ] . | schema_name . ] table_na

Hi,
Create Schema.TableName (No Problem)
Create Schema1.schema2.TableName (Problem SQL see schema1 as the database)
Create Database,schema1.schema2.TableName (Error below)
Msg 117, Level 15, State 1, Line 13
The object name 'DatabasName.Schema1. Schema2.TableName' contains more than
the maximum number of prefixes. The maximum is 2.
Msg 319, Level 15, State 1, Line 84
Incorrect syntax near the keyword 'with'. If this statement is a common
table expression or an xmlnamespaces clause, the previous statement must be
terminated with a semicolon.
"Aaron Bertrand [SQL Server MVP]" wrote:

> Can you show the ACTUAL syntax you tried, and the EXACT error message?
> The syntax you show demonstrates two options:
> CREATE TABLE master.schema1.table
> CREATE TABLE schema1.table
> It sounds like you maybe misinterpreted it as:
> CREATE TABLE master.schema1.schema2.table
> ?
>
>
>> Create Schema1.schema2.TableName (Problem SQL see schema1 as the database)
Yes, because you can't nest schemas.

> Create Database,schema1.schema2.TableName (Error below)
You still can't nest schemas. A schema can't own a schema, there is only
one "level" for it in the heirarchy.|||You can nest schemas according to the syntax. Do you claim the syntax is wro
ng?
Syntax
CREATE TABLE
[ database_name . [ schema_name ] . | schema_name . ] table_name
"Aaron Bertrand [SQL Server MVP]" wrote:

> Yes, because you can't nest schemas.
>
> You still can't nest schemas. A schema can't own a schema, there is only
> one "level" for it in the heirarchy.
>
>|||> You can nest schemas according to the syntax. Do you claim the syntax is
> wrong?
> Syntax
> CREATE TABLE
> [ database_name . [ schema_name ] . | schema_name . ] table_name
No, the syntax is not wrong. Your interpretation of the syntax is wrong, as
several people have already pointed out.
The syntax says you can do:
CREATE TABLE databasename.schemaname.tablename
or
CREATE TABLE schemaname.tablename
or
CREATE TABLE tablename
[[this.that]|or this but not both.]tablename
Go ahead and file a bug that the documentation is wrong as per your
interpretation. But the following fact remains the same: YOU CANNOT NEST
SCHEMAS, EVEN IF YOU INTERPRET THE DOCUMENTATION INCORRECTLY.|||I think you only have to change the parameter from 2 to 3 (Error message),
and we are fine.
I found 1 person by searching the Internet who had the error 3, who tried to
write 3 schemas by including dbo in his syntax...
Thank you,
Rune
"Aaron Bertrand [SQL Server MVP]" wrote:

> No, the syntax is not wrong. Your interpretation of the syntax is wrong,
as
> several people have already pointed out.
> The syntax says you can do:
> CREATE TABLE databasename.schemaname.tablename
> or
> CREATE TABLE schemaname.tablename
> or
> CREATE TABLE tablename
> [[this.that]|or this but not both.]tablename
> Go ahead and file a bug that the documentation is wrong as per your
> interpretation. But the following fact remains the same: YOU CANNOT NEST
> SCHEMAS, EVEN IF YOU INTERPRET THE DOCUMENTATION INCORRECTLY.
>
>|||>I think you only have to change the parameter from 2 to 3 (Error message),
> and we are fine.
> I found 1 person by searching the Internet who had the error 3, who tried
> to
> write 3 schemas by including dbo in his syntax...
Now I have absolutely no idea what you are talking about.
In summary: YOU CANNOT NEST SCHEMAS.