Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Need help writng a do while loop in 2008 Sql server Expand / Collapse
Author
Message
Posted Monday, July 28, 2014 9:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 12:21 PM
Points: 7, Visits: 8
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:
PLANT PLANT_DESC VENDOR VENDOR_NAME
NOPE NULL NOPE NOPE
7010 Coquitlam DC PR1 -100
NOPE NULL NOPE NOPE
NOPE NULL NOPE NOPE
NOPE NULL NOPE NOPE
NOPE NULL NOPE NOPE
NOPE NULL 1014435 COCA-COLA NORTH AMERI
NOPE NULL 1014435 COCA-COLA NORTH AMERI
NOPE NULL 1014435 COCA-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
Post #1596932
Posted Monday, July 28, 2014 10:00 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 5:44 PM
Points: 946, Visits: 2,739
Why do you need to do this using a loop? What have you tried so far?

Shawn Melton
PS C:\>(Find-Me).TwitterURL
@wsmelton
PS C:\>(Find-Me).BlogURL
meltondba.wordpress.com
Post #1596937
Posted Monday, July 28, 2014 10:00 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:33 PM
Points: 12,953, Visits: 32,483
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,'NOPE','NOPE' UNION ALL
SELECT 'NOPE',NULL,'NOPE','NOPE' 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' 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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1596938
Posted Monday, July 28, 2014 10:19 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 12:21 PM
Points: 7, Visits: 8
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
Post #1596955
Posted Monday, July 28, 2014 10:43 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 9:40 AM
Points: 6,842, Visits: 13,372
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
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #1596964
Posted Monday, July 28, 2014 10:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 12:21 PM
Points: 7, Visits: 8
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
Post #1596968
Posted Monday, July 28, 2014 11:59 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:33 PM
Points: 12,953, Visits: 32,483
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1597003
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse