cursor to compare/report on the same fields in 2 tables

[SQLNewbie]

I have the following cursor that I am comparing 2 tables, the
production table and a copy of the production table, I want results of
all address's that are like the address1 field...the problem is...my
results are giving me every field and if there is more than one, it is
putting it in a grid....

I only want to see results if they are 1 for the same address field

this is what I have so far....
declare @address1 char(61),@city char(61)

declare address_cursor CURSOR FOR
SELECT address1,city FROM test.dbo.testadd

OPEN address_cursor

fetch next from address_cursor into @address1,@city
while @@fetch_status = 0
BEGIN
select * from testadd where @address1 like '%' + address1 + '%' and
@city = city
Fetch next from address_cursor into @address1,@city
Print
END
CLOSE address_cursor
DEallocate address_cursor

[Alexander Kuznetsov]

http://sqlserver-puzzles.blogspot.co...lbes-real.html

-----------------------
Alex Kuznetsov
http://sqlserver-tips.blogspot.com/
http://sqlserver-puzzles.blogspot.com/

[David Portas]

SQLNewbie wrote:

I have the following cursor that I am comparing 2 tables, the
production table and a copy of the production table, I want results of
all address's that are like the address1 field...the problem is...my
results are giving me every field and if there is more than one, it is
putting it in a grid....

I only want to see results if they are 1 for the same address field

this is what I have so far....

[snip]

You refer to yourself as a "newbie". If you are new to SQL or to SQL
Server then do not even attempt to write cursors.

There are nearly always better alternatives to cursors. It's only when
you have a lot of experience that you can make an informed decision
about when a cursor makes sense. Meantime, if you can't think of
another way to do something it would be better to ask for help rather
than try to write a cursor. You'll learn good practices a LOT quicker
that way.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/m...S,SQL.90).aspx
--

[Erland Sommarskog]

SQLNewbie (ta************@bancsourceinc.com) writes:

I have the following cursor that I am comparing 2 tables, the
production table and a copy of the production table, I want results of
all address's that are like the address1 field...the problem is...my
results are giving me every field and if there is more than one, it is
putting it in a grid....

I only want to see results if they are 1 for the same address field

If it's a copy, isn't it the same data then?

I was trying to understand what you really want to do, but I'm afraid I
don't.

I would suggest that you post:

o CREATE TABLE statements for your tables, preferably to show the
pertinent points.
o INSERT statements with sample data.
o The desired output given the sample.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Ed Murphy]

SQLNewbie wrote:

I have the following cursor that I am comparing 2 tables, the
production table and a copy of the production table, I want results of
all address's that are like the address1 field...the problem is...my
results are giving me every field and if there is more than one, it is
putting it in a grid....

I only want to see results if they are 1 for the same address field

this is what I have so far....
declare @address1 char(61),@city char(61)

declare address_cursor CURSOR FOR
SELECT address1,city FROM test.dbo.testadd

OPEN address_cursor

fetch next from address_cursor into @address1,@city
while @@fetch_status = 0
BEGIN
select * from testadd where @address1 like '%' + address1 + '%' and
@city = city
Fetch next from address_cursor into @address1,@city
Print
END
CLOSE address_cursor
DEallocate address_cursor

See if this points you in the right direction:

select t.address1, t.city, count(*)
from test.dbo.testadd t
join production.dbo.testadd p on t.city = p.city
where p.address1 like '%' + t.address1 + '%'
group by t.address1, t.city
having count(*) 1