declare @cmdstr varchar(255)
select @cmdstr = 'isql -E -w255 -n -h-1 ' +
'-Q"select * from pubs..authors where state in (''KS'', ''MD'')"'
create table #t1 (row varchar(255))
insert #t1 exec master..xp_cmdshell @cmdstr
delete #t1 where row is null
select * from #t1
/*
The output of ISQL by separates column values with spaces & hence it is easy to
compare the rows as is. This is like comparing two long varchar( 255) strings.
*/
/* Part of the output is shown below:
row
-----------------------------------------------------------------------------------------------------------
341-22-1782 Smith Meander 913 843-0462 10 Mississippi Dr.
807-91-6654 Panteley Sylvia 301 946-8853 1956 Arlington Pl.
*/
select @cmdstr = 'isql -E -w255 -n -h-1 ' +
'-Q"select * from pubs..authors where state in (''TN'', ''MD'')"'
create table #t2 (row varchar(255))
insert #t2
exec master..xp_cmdshell @cmdstr
delete #t2 where row is null
select * from #t2
/* Part of the output:
row
-------------------------------------------------------------------------------------------------------------
527-72-3246 Greene Morningstar 615 297-2723 22 Graybar House Rd.
807-91-6654 Panteley Sylvia 301 946-8853 1956 Arlington Pl.
*/
-- To get the rows existing only in #t1
select * from #t1
where not exists(select * from #t2 where #t1.row = #t2.row)
/*
row
-----------------------------------------------------------------------------------------------------------
341-22-1782 Smith Meander 913 843-0462 10 Mississippi Dr.
*/
-- To get the rows existing only in #t2
select * from #t2
where not exists(select * from #t1 where #t1.row = #t2.row)
/*
row
-------------------------------------------------------------------------------------------------------------
527-72-3246 Greene Morningstar 615 297-2723 22 Graybar House Rd.
*/
go
drop table #t1
drop table #t2
This page was last updated on May 01, 2006 04:28 PM.