Comparing the Contents of Two Tables

Comparing the Contents of Two Tables:

create table a
(
c1 int,
c2 varchar(3),
c3 varchar(3)
)


create table b
(
c1 int,
c2 varchar(3),
c3 varchar(3)
)

insert into a values(1,'x','y')
insert into a values(2,'xx','y')
insert into a values(3,'x','y')

insert into b values(1,'x','y')
insert into b values(2,'x','y')
insert into b values(3,'x','yy')

select * from a

C1 C2 C3
----- -- --
1 x y
2 xx y
3 x y

select * from b
C1 C2 C3
----- -- --
1 x y
2 x y
3 x yy


The first rows are the same, but the second and third rows differ. This is how we can find them:

select i.c1,i.c2, i.c3,
count(i.src1) CNT1,
count(i.src2) CNT2
from
( select a.*,
1 src1,
null as src2
from a
union all
select b.*,
null as src1,
2 src2
from b
)i
group by i.c1,i.c2,i.c3
having count(i.src1) <> count(i.src2)

C1 C2 C3 CNT1 CNT2
--- -- -- ---- ----
2 x y 0 1
2 xx y 1 0
3 x y 1 0
3 x yy 0 1

The below link having the script in Oracle:

http://karthikeyanbaskaran.blogspot.com/2010/02/comparing-contents-of-two-tables.html

Read More

Spool operation in SQL server

SQL Server doesn't have a SPOOL command like Oracle does for writing to files,
But there are other ways of doing what we want.

1. For reading, use xp_cmdshell and the ECHO command. Use the > or >> redirection symbols to either create or append to a file.

xp_cmdshell "@ECHO test message >> C:\file.fil"

2. Put the information you want into a table (note this can't be an ordinary temporary table, but it can be a global temporary table) and then bcp it out to a file via xp_cmdshell.

xp_cmdshell "bcp .. out c:\file.fil -Usa -P -c"

3. Run the select through ISQL via xp_cmdshell and use the -o parameter to output the results to a file. This example uses the -E parameter to avoild hard-coding a userid.

declare @str varchar(255)
select @str = 'isql -Q"select * from " -E -oc:\file.fil'
exec master..xp_cmdshell @str

Read More