How do you loop through SQL Server records in a ta

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

SQL Server Development (2000)

How do you loop through SQL Server records in a ta

Author

Topic

gvd
Starting Member

21 Posts

Posted - 2005-08-02 : 16:00:57

Hello guys,
Please how do i write an sql query that will iterate through the records in a table from the first record through the last record?

Or what function can i use that will take me from the first record through to the last record, so that i can do some data manipulation.

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-08-02 : 16:04:33

You can use a WHILE loop to do this. However, why do you want to do this? This will be very, very slow. You should be doing data manipulation in a set-based approach.

Tara

gvd
Starting Member

21 Posts

Posted - 2005-08-02 : 16:13:33

Thanks tduggan,
but sorry am a newbie.
how do you mean i "should be doing data manipulation in a set-based approach." Can you throw more light on this or give me a practical example, using some T-Sql reserved words, i'll be a ble to grab it better like that.
Thanks once again

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-08-02 : 16:16:12

We would need more information as to what you are trying to do in order to point you in the right direction.

But here's an example. Let's say we want to update a table named Table1. And we want to update every row in that table so that a column named Column1 would now equal 'Tara'. Here is what we would do:

UPDATE Table1
SET Column1 = 'Tara'

We would not loop through each row to do this. This is a very simplified example as you probably know. But it's hard to tell you what the set-based approach is going to be for your problem if we don't know the details of your problem. Please see this link on how to post table structions and sample data if that is what you'll need to do to explain:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=29090

Tara

gvd
Starting Member

21 Posts

Posted - 2005-08-02 : 16:28:41

Thanks tduggan,
should i script out the table and then tell u exactly wat i am trying to do?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-08-02 : 16:30:51

See the link for how we need the information posted. In the link in the first post of the link I posted above, it shows you how to post the table plus sample data and the expected result set. Sample data is typically less than 10 rows but it should represent the problem you are trying to solve. The table does not need to be scripted with all of the columns, just the columns that illustrate your problem. And the expected result set needs to match what the result set will look like after we provide a working query against the sample data that you provided.

Tara

gvd
Starting Member

21 Posts

Posted - 2005-08-02 : 18:05:32

i hope this helps. Thanks

I have a table with two columns:
FacultyName, FacultyCode.
The table has about 10,000 records. And i want to update all of the faculty code to a particular format.

The present data in the 'FacultyCode' field is in this format:
01-2345-6789-45
12-9636-4555-78
12-5450-8500-78

and i want to update to this format:
0123456789-45
1296364555-78
1254508500-78

i.e. i want to remove the first two hyphens from the from the data in that field.

thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-08-02 : 18:16:19

Will the first two hyphens always be in the same location in the column?

Tara

gvd
Starting Member

21 Posts

Posted - 2005-08-02 : 18:21:44

yes tduggan,
the first two hyphens will always be in the same location

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-08-02 : 18:26:25

Here you go:



SET NOCOUNT ON

CREATE TABLE Table1 (Column1 varchar(20))

INSERT INTO Table1 VALUES('01-2345-6789-45')
INSERT INTO Table1 VALUES('12-9636-4555-78')
INSERT INTO Table1 VALUES('12-5450-8500-78')

SELECT * FROM Table1

UPDATE Table1
SET Column1 = REPLACE(SUBSTRING(Column1, 1, 8), '-', '') + SUBSTRING(Column1, 9, DATALENGTH(Column1))

SELECT * FROM Table1

DROP TABLE Table1

So I used the REPLACE function to get rid of the - in the first 8 characters using SUBSTRING 1-8, then concatenated that with the rest of the string using SUBSTRING 9-end of the column.

Tara

gvd
Starting Member

21 Posts

Posted - 2005-08-02 : 19:02:29

THANKS A BUNCH TDUGGAN

YOU ARE THE MAN

gvd
Starting Member

21 Posts

Posted - 2005-08-02 : 19:03:41

TDuggan,

U really must be some kind of Goddess, it worked effortlessly

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources