Looping through a table - 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)

Looping through a table

Author

Topic

SarahLOR
Starting Member

18 Posts

Posted - 2010-03-31 : 12:22:58

OK, simple question.....I come from an Oracle background, so would be able to do this in PLSQL.
I need to loop through a table, and would use a FOR LOOP in Oracle, using something like the code below. Would somebody please be able to tell me how I would do this in SQL Server?

CREATE OR REPLACE PROCEDURE p_Upload AS

CURSOR c_Upload IS
(SELECT *
FROM Upload);

BEGIN
FOR r_Upload IN c_Upload LOOP

INSERT INTO Employers ( ClassId, Created, Status, Code, Name)
SELECT Cls.ClassID,
sysdate,
'1',
r_Upload.code,
r_Upload.Name
FROM Classes Cls
WHERE Cls.Name = 'TEST';

END LOOP;
END;

Thanks,
Sarah

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2010-03-31 : 12:29:32

For as simple requirement as that, you don't need cursor and loop at all in sql server. Here is how you do it in SQL server:


INSERT INTO Employers ( ClassId, Created, Status, Code, Name)
Select Cls.ClassID,
getdate(),
'1',
r.code,r.Name
FROM Classes Cls CROSS JOIN Upload r
WHERE Cls.Name = 'TEST'

Here, I am assuming that there is no relation between table Classes and table Upload, hence the cross join. If these tables are related you can use inner join to join based on key fields.

Harsh Athalye
http://www.letsgeek.net/

SarahLOR
Starting Member

18 Posts

Posted - 2010-03-31 : 12:37:12

The Upload table is created from a CSV file, so I have a number of tables that I need to insert into, just included 1 in the example. Would you just use the CROSS JOIN for each of the tables? Or would you loop through each record and then do the inserts?

Thanks

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2010-03-31 : 12:43:31

If there is no relational dependency, CROSS JOIN is what you will have to use. Cursor and looping is not recommended in SQL server due to performance bottleneck.

Harsh Athalye
http://www.letsgeek.net/

SarahLOR
Starting Member

18 Posts

Posted - 2010-03-31 : 12:44:55

Seems even easier than Oracle then!

Thats great, thanks for your help.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2010-03-31 : 12:52:41

Please be aware however that CROSS JOIN may result in bloated data since there is no way for SQL to know which record to map with which other record. Hence, it will return list of all possible combinations. If you have more than one record in upload table, it will result in bloating of data while insertion.

Harsh Athalye
http://www.letsgeek.net/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources