Need help writng a do while loop in 2008 Sql server

Question

Post reply

Need help writng a do while loop in 2008 Sql server

thejordans21

SSC Enthusiast

Points: 141
More actions
July 28, 2014 at 9:55 am

#289070

Hello I need some help writing a loop in 2008 Sql server. I am just begining to use this and need some help;
Here is my data:
PLANTPLANT_DESCVENDORVENDOR_NAME
NOPENULLNOPENOPE
7010Coquitlam DCPR1 -100
NOPENULLNOPENOPE
NOPENULLNOPENOPE
NOPENULLNOPENOPE
NOPENULLNOPENOPE
NOPENULL1014435COCA-COLA NORTH AMERI
NOPENULL1014435COCA-COLA NORTH AMERI
NOPENULL1014435COCA-COLA NORTH AMERI
I need a loop to pouplate the PLANT and PLANT_DESC with 7010 and Coquitlam repesctly where the vendor isn't NOPE. Any help would be greatly appreicated
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Shawn Melton SSC-Insane Points: 24685 More actions · Answer 1

Why do you need to do this using a loop? What have you tried so far?

Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton

Lowell SSC Guru Points: 323504 More actions · Answer 2

the beauty of SQL server is set based operations.

that means you affect all records, without a loop, as long as they meet a specific criteria.

IF OBJECT_ID('tempdb.[dbo].[#MyData]') IS NOT NULL

DROP TABLE [dbo].[#MyData]

GO

CREATE TABLE [dbo].[#MyData] (

[PLANT] VARCHAR(30) NULL,

[PLANT_DESC] VARCHAR(30) NULL,

[VENDOR] VARCHAR(30) NULL,

[VENDOR_NAME] VARCHAR(30) NULL)

--my initial data

INSERT INTO #MyData

SELECT 'NOPE',NULL,'NOPE','NOPE' UNION ALL

SELECT '7010',NULL,'Coquitlam DC','PR1 -100' UNION ALL

SELECT 'NOPE',NULL,'NOPE','NOPE' UNION ALL

SELECT 'NOPE',NULL,'1014435','COCA-COLA NORTH AMERI' UNION ALL

SELECT 'NOPE',NULL,'1014435','COCA-COLA NORTH AMERI'

SELECT * FROM #MyData

UPDATE #MyData

SET PLANT = '7010',

PLANT_DESC='Coquitlam'

WHERE VENDOR <> 'NOPE';

SELECT * FROM #MyData

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

thejordans21 SSC Enthusiast Points: 141 More actions · Answer 3

There is more data that that but put that just for a start.. I need to populate all the rows with the plant and plant desrc. I wanted to use a loop becuase there is alot more data

LutzM SSC Guru Points: 107049 More actions · Answer 4

thejordans21 (7/28/2014)
There is more data that that but put that just for a start.. I need to populate all the rows with the plant and plant desrc. I wanted to use a loop becuase there is alot more data

Especially if there are more data a set based solution should be used.

Except it doesn't matter at all how long it takes to return the results...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

thejordans21 SSC Enthusiast Points: 141 More actions · Answer 5

I don;t know how to set that up. Iout the plant and plant desc into another temp table and I am trying to use that to popllaute the orginal temp table

Lowell SSC Guru Points: 323504 More actions · Answer 6

thejordans21 (7/28/2014)
I don;t know how to set that up. Iout the plant and plant desc into another temp table and I am trying to use that to popllaute the orginal temp table

the temp table was so that you could actualyl see a set based example.

i kind of know your column names, assuming what you posted was correct.

the example i posted would work whether there was a million rows or just one.

for example, if you run this command, changing the YourTableName to your Real Table, and you look at the data, would that match the rows you want to affect?

SELECT * FROM YourTableName WHERE VENDOR <> 'NOPE';

a set base doperation would modify the same rows that command returns in a single command...

that's the mental shift from programming a process (row-by-agaonizing row) to a set based operation: instead of updating a million things one at a time, you use a set based operation once to do a million updates.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!