sql loop - 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 2005 Forums

Transact-SQL (2005)

sql loop

Author

Topic

craigmacca
Posting Yak Master

142 Posts

Posted - 2010-06-29 : 05:39:22

Hi i have a select statement which populates a temp table

from this temp table i need to loop through it and then insert

1. into customer table
2. into details table (this has a customerId so i will need a @@identity)

i am just not sure how to get the values out of the loop for the inserts?

my code below is just a test to get it working, i will then be adding more fields.

-- create the temp table
Create Table #tempItemTable
(memberNumber varchar(50), temp_id int identity(1,1))

-- first get a list of pro members that are new in max in hold them in a temp table
insert into #tempItemTable
select npo.CONTACTNO
from Members npo
left join Dir_Customers dc ON dc.MembershipNumber = npo.CONTACTNO
where dc.MembershipNumber is not null

-- new we have a list loop through and insert in to customers and business directory

DECLARE @LoopId int

DECLARE my_cursor CURSOR FOR
SELECT memberNumber, surname, temp_id FROM #tempItemTable

OPEN my_cursor

FETCH NEXT FROM my_cursor
INTO @LoopId

WHILE @@FETCH_STATUS = 0
BEGIN

declare @newId int

INSERT INTO customer
(memberNumber, surname)
VALUES
(@LoopId, ???)

@newId = @@identity

INSERT INTO details
(customerId, surname)
VALUES
(@newId, ???)

FETCH NEXT FROM my_cursor
INTO @LoopId
END

CLOSE my_cursor
DEALLOCATE my_cursor

sql-programmers
Posting Yak Master

190 Posts

Posted - 2010-06-30 : 05:44:42

Try the script,

-- create the temp table
Create Table #tempItemTable
(memberNumber varchar(50), temp_id int identity(1,1),surname varchar(50))

-- first get a list of pro members that are new in max in hold them in a temp table
insert into #tempItemTable
select npo.CONTACTNO,nposurname
from Members npo
left join Dir_Customers dc ON dc.MembershipNumber = npo.CONTACTNO
where dc.MembershipNumber is not null

-- new we have a list loop through and insert in to customers and business directory

DECLARE @memberNumber varchar(50)
DECLARE @surname varchar(50)
DECLARE @newId int

DECLARE my_cursor CURSOR FOR
SELECT memberNumber, surname FROM #tempItemTable

OPEN my_cursor

FETCH NEXT FROM my_cursor
INTO @memberNumber,@surname

WHILE @@FETCH_STATUS = 0
BEGIN

INSERT INTO customer
(memberNumber, surname)
VALUES
(@memberNumber, @surname)

set @newId = @@identity

INSERT INTO details
(customerId, surname)
VALUES
(@newId, @surname)

FETCH NEXT FROM my_cursor
INTO @memberNumber,@surname
END

CLOSE my_cursor
DEALLOCATE my_cursor

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources