Iterating through query results

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)

Iterating through query results

Author

Topic

Dman
Starting Member

2 Posts

Posted - 2004-08-04 : 10:31:06

I need to make updates/inserts/deletes to a database based on one of the values retrieved.

For instance:

Select Flag, MemberID, LastName, FirstName FROM #tmpTable

The Flag value will be either an 'A', 'C', or 'D'.
If it is an 'A' I need to insert that row into a table. If it is a 'C' I need to make an update to a table. If it is a 'D' I need to delete that row from a table. All of this without a cursor

Any help would be appreciated.

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2004-08-04 : 10:38:05

You can use lables and a case statment.

------------->>> BREAKING NEWS!!! <<<-------------

Saddam Hussien has weapons of mass destrcution

raymondpeacock
Constraint Violating Yak Guru

367 Posts

Posted - 2004-08-04 : 10:38:47

How about something like this:

INSERT tablename
SELECT MemberId, LastName, FirstName
FROM #tmpTable
WHERE Flag = 'A'

UPDATE t
FROM tablename t
JOIN #tmptable t2 ON t.MemberId = t2.MemberId
SET LastName = t2.LastName, FirstName = t2.FirstName
WHERE t2.Flag = 'C'

DELETE t
FROM tablename t
JOIN #tmptable t2 ON t.MemberId = t2.MemberId
WHERE t2.Flag = 'D'

Raymond

Dman
Starting Member

2 Posts

Posted - 2004-08-04 : 10:44:12

That would work raymond, however, I need to run them in the order that they're in the database.

raymondpeacock
Constraint Violating Yak Guru

367 Posts

Posted - 2004-08-04 : 10:45:50

Why? Would and ORDER BY on each clause give you what you want?

Raymond

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources