Looping through recordset - 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

General SQL Server Forums

New to SQL Server Programming

Looping through recordset

Author

Topic

fralo
Posting Yak Master

161 Posts

Posted - 2009-08-28 : 14:10:09

I need help in looping through a recordset and inserting each individual row into a table. Could someone please help me with the syntax for this? Here is my stored procedure. Thanks so much for your help.

CREATE PROCEDURE IAPRO_build
AS
BEGIN
DECLARE @OFFNUM INT
declare @i int
set @i = 1

SET NOCOUNT ON;

DELETE FROM IADATA.IA_ADM.OFFICERS

set @OFFNUM = (SELECT * FROM OPENQUERY([ESO-CAD], 'SELECT COUNT
(*) FROM EMPMAST.DBO.EMPMAST'))

while @i < @OFFNUM
begin

--Here I need to select * from table. Loop through
--recordset and individually insert row into another table.

SET @i = @i + 1
END

END
GO

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-08-28 : 14:19:09

There is no need to loop...you can just do this..

INSERT INTO <table_name> (field1,field2)
SELECT field1,field2 FROM EMPMAST.DBO.EMPMAST

fralo
Posting Yak Master

161 Posts

Posted - 2009-08-28 : 14:31:57

Well, the reason why I need to loop is because I have an incremental value that will be the primary key of each record. In the above code, it is 'i'. So unless I have an explicit statement for each record queried and then increment it each time, I can't do that.

Kind of like...

LOOP
--INSERT INTO TABLE VALUES (i, column, column, etc.)
--INCREMENT VALUE - i + 1
END LOOP

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-08-28 : 14:43:44

in that case...try something like this..

INSERT INTO <table_name> 
(i,field1,field2)
SELECT row_number() over(order by <column>) as i,
field1,
field2 
FROM EMPMAST.DBO.EMPMAST

EDIT : or you can define the column "i" as an IDENTITY ..so you dont have to worry about it anymore.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources