Select into - 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 2000 Forums

Transact-SQL (2000)

Select into

Author

Topic

schmell
Starting Member

2 Posts

Posted - 2003-05-14 : 15:20:47

HI

I have declared a cursor, then within that cursor loop, I would like to perform another select from a different table into variables to compare. Is a select into not feasble?

DECLARE consultant_cursor CURSOR FOR

Select cnsltnt_id, address1, address2, city, st, zip,
cntry, curr_dt_ts
from dbo.gr_dist_main
order by cnsltnt_id

OPEN Consultant_Cursor

FETCH NEXT FROM Consultant_Cursor
INTO @cnsltnt_id, @addr_line_1, @addr_line_2, @city, @st, @pstl_cd, @cntry, @curr_dt_ts

WHILE @@FETCH_STATUS = 0
BEGIN

set @country_id = (select country_id
from dw_country_tbl
where country_cd = @cntry)

begin

*THIS IS WHAT IS NOT WORKING
Select cnsltnt_id , addr_line_1 , addr_line_2, city,
st, pstl_cd, country_id
into @tbl_cnsltnt_id, @addr_line_1, @tbl_addr_line_2, @tbl_city, @tbl_st, @tbl_pstl_cd, @tbl_country_id
from dbo.gr_cnsltnt_addr_tbl t
where coa_dt = (select max(coa_dt) from dbo.gr_cnsltnt_addr_tbl t1
where t.cnsltnt_id = t1.cnsltnt_id and
t.cnsltnt_id = @cnsltnt_id)
end

IF @cnsltnt_id = @tbl_cnsltnt_id and
(@addr_line_1 <> @tbl_addr_line_1 or
@addr_line_2 <> @tbl_addr_line_2 or
@city <> @tbl_city or
@st <> @tbl_st or
@pstl_cd <> @tbl_pstl_cd or
@country_id <> @tbl_country_id)

yakoo
Constraint Violating Yak Guru

312 Posts

Posted - 2003-05-14 : 15:25:44

have you tried


Select @tbl_cnsltnt_id = cnsltnt_id , @tbl_addr_line_1 = addr_line_1 , @tbl_addr_line_2 = addr_line_2, @tbl_city = city, 
@tbl_st = st, @tbl_pstl_cd = pstl_cd, @tbl_country_id = country_id 
from dbo.gr_cnsltnt_addr_tbl t 
where coa_dt = (select max(coa_dt) from dbo.gr_cnsltnt_addr_tbl t1 
where t.cnsltnt_id = t1.cnsltnt_id and 
t.cnsltnt_id = @cnsltnt_id)

Also you should try to avoid cursors as much as possible.

Got SQL?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources