How do I loop through rows and populate data?

Question

Post reply

How do I loop through rows and populate data?

thejordans21

SSC Enthusiast

Points: 161
More actions
August 1, 2014 at 12:35 pm

#297223

Here is my data.. (I have around 195K of rows)
Here is a brief snapshot of it:PLANTPLANT_DESCVENDOR
NOPENULLNOPE
7010Coquitlam DCPR1
NOPENULLNOPE
NOPENULLNOPE
NOPENULLNOPE
NOPENULLNOPE
NOPENULL1014435
NOPENULL1014435
NOPENULL1014435
NOPENULLNOPE
NOPENULLNOPE
NOPENULLNOPE
NOPENULL1014435
NOPENULL1014435
NOPENULL1014435
What I need to happen is that where there is a number for Vendor I need the plant and plant number populated. I have created a temp table that has all the plant and plant desrc in it. The plant and plant desc change further down the lines of data so my next step would be to acocunt for that
any suggestions?

Viewing 9 posts - 1 through 9 (of 9 total)

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

thejordans21 SSC Enthusiast Points: 161 More actions · Answer 1

INSERT INTO Temp_OPENPO1

SELECT A.PLANT, A.PLANT_DESC FROM

Temp_OPENPO A

WHERE VENDOR = 'PR1'

this is my command to populate the temp table

thejordans21 SSC Enthusiast Points: 161 More actions · Answer 2

upDATE Temp_OPENPO

SET PLANT_DESC = Temp_OPENPO1.PLANT_DESC

FROM Temp_OPENPO1

WHERE Temp_OPENPO.VENDOR <> 'NONE'

I have this to try and update the temp_OPENPO but it is populating every single row

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 3

thejordans21 (8/1/2014)
Here is my data.. (I have around 195K of rows)
Here is a brief snapshot of it:PLANTPLANT_DESCVENDOR
NOPENULLNOPE
7010Coquitlam DCPR1
NOPENULLNOPE
NOPENULLNOPE
NOPENULLNOPE
NOPENULLNOPE
NOPENULL1014435
NOPENULL1014435
NOPENULL1014435
NOPENULLNOPE
NOPENULLNOPE
NOPENULLNOPE
NOPENULL1014435
NOPENULL1014435
NOPENULL1014435
What I need to happen is that where there is a number for Vendor I need the plant and plant number populated. I have created a temp table that has all the plant and plant desrc in it. The plant and plant desc change further down the lines of data so my next step would be to acocunt for that
any suggestions?

What is the PK for this table?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eirikur Eiriksson SSC Guru Points: 182948 More actions · Answer 4

You might want to try something like this:

😎

USE tempdb;

GO

CREATE TABLE dbo.TBL_PLANT

(

PLANT_ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_DBO_PLANT_PLANT_ID PRIMARY KEY CLUSTERED

,PLANT VARCHAR(50) NOT NULL

,PLANT_DESC VARCHAR(100) NULL

,VENDOR VARCHAR(50) NOT NULL

);

CREATE TABLE dbo.TBL_VENDOR

(

VENDOR_ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_DBO_VENDOR_VENDOR_ID PRIMARY KEY CLUSTERED

,VENDOR VARCHAR(50) NOT NULL

,PLANT VARCHAR(50) NOT NULL

,PLANT_DESC VARCHAR(100) NULL

);

INSERT INTO dbo.TBL_VENDOR (VENDOR,PLANT,PLANT_DESC)

VALUES ('1014435','Vendor 1014436 plant','Vendor 1014436 plant description')

,('1014436','Vendor 1014436 plant','Vendor 1014436 plant description');

INSERT INTO dbo.TBL_PLANT (PLANT,PLANT_DESC,VENDOR)

VALUES

('NOPE',NULL,'NOPE')

,('7010','Coquitlam DC','PR1')

,('NOPE',NULL,'NOPE')

,('NOPE',NULL,'1014435')

,('NOPE',NULL,'NOPE')

,('NOPE',NULL,'1014435')

,('NOPE',NULL,'1014435');

UPDATE P

SET P.PLANT = V.PLANT

,PLANT_DESC = V.PLANT_DESC

FROM dbo.TBL_PLANT P

INNER JOIN dbo.TBL_VENDOR V

ON P.VENDOR = V.VENDOR

SELECT * FROM dbo.TBL_PLANT

DROP TABLE dbo.TBL_VENDOR;

DROP TABLE dbo.TBL_PLANT;

Results

PLANT_ID PLANT PLANT_DESC VENDOR

----------- ----------------------- ---------------------------------- ---------

1 NOPE NULL NOPE

2 7010 Coquitlam DC PR1

3 NOPE NULL NOPE

4 NOPE NULL NOPE

5 NOPE NULL NOPE

6 NOPE NULL NOPE

7 Vendor 1014436 plant Vendor 1014436 plant description 1014435

8 Vendor 1014436 plant Vendor 1014436 plant description 1014435

9 Vendor 1014436 plant Vendor 1014436 plant description 1014435

10 NOPE NULL NOPE

11 NOPE NULL NOPE

12 NOPE NULL NOPE

13 Vendor 1014436 plant Vendor 1014436 plant description 1014435

14 Vendor 1014436 plant Vendor 1014436 plant description 1014435

15 Vendor 1014436 plant Vendor 1014436 plant description 1014435

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Being a visually oriented person, I'd need to see what the OP is expecting the results to be from the query.

thejordans21 SSC Enthusiast Points: 161 More actions · Answer 6

I was able to get the info into the second temp table OPENPO1

here is a my raw data (partial)

PLANTPLANT_DESCVENDOR

7010Coquitlam DCPR1

NOPENULLNOPE

NOPENULL1014435

7012Chilliwack DCPR1

NOPENULLNOPE

NOPENULLPL7087

NOPENULLNOPE

NOPENULLPL7029

I need a loop or something so that afterit run the data will look like this:

PLANTPLANT_DESCVENDOR

7010Coquitlam DCPR1

NOPENULLNOPE

7010Coquitlam DC1014435

7012Chilliwack DCPR1

NOPENULLNOPE

7012Chilliwack DCPL7087

NOPENULLNOPE

7012Chilliwack DCPL7029

I am new to using loops and if else statemets. any help would be greatly apperciated

Sean Lange SSC Guru Points: 286609 More actions · Answer 7

thejordans21 (8/5/2014)
I was able to get the info into the second temp table OPENPO1
here is a my raw data (partial)
PLANTPLANT_DESCVENDOR
7010Coquitlam DCPR1
NOPENULLNOPE
NOPENULLNOPE
NOPENULLNOPE
NOPENULLNOPE
NOPENULL1014435
NOPENULL1014435
NOPENULL1014435
7012Chilliwack DCPR1
NOPENULLNOPE
NOPENULLNOPE
NOPENULLNOPE
NOPENULLNOPE
NOPENULLPL7087
NOPENULLNOPE
NOPENULLNOPE
NOPENULLNOPE
NOPENULLNOPE
NOPENULLNOPE
NOPENULLNOPE
NOPENULLPL7029
NOPENULLPL7029
I need a loop or something so that afterit run the data will look like this:
PLANTPLANT_DESCVENDOR
7010Coquitlam DCPR1
NOPENULLNOPE
NOPENULLNOPE
NOPENULLNOPE
NOPENULLNOPE
7010Coquitlam DC1014435
7010Coquitlam DC1014435
7010Coquitlam DC1014435
7012Chilliwack DCPR1
NOPENULLNOPE
NOPENULLNOPE
NOPENULLNOPE
NOPENULLNOPE
7012Chilliwack DCPL7087
NOPENULLNOPE
NOPENULLNOPE
NOPENULLNOPE
NOPENULLNOPE
NOPENULLNOPE
NOPENULLNOPE
7012Chilliwack DCPL7029
7012Chilliwack DCPL7029
I am new to using loops and if else statemets. any help would be greatly apperciated

Looping is not the answer here. You can do this with a single statement. Something along the lines of what Eirikur posted. If that doesn't work then you need to post the table and ddl in such a way that we can consume it.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

Well, that's good. Too bad there is nothing there to indicate order of rows.

Really need to start at step one, which is to read the first article I link to below in my signature block. It will walk you through everything you need to post and how to post it to get the best possible responses.

Right now there really is nothing we can to help with this issue. You need to realize that without some identifying value(s), there is no order to the data in the table. I can see a possible way to accomplish the task, but there is nothing in the table that will support the process as there is nothing to ensure the order of the rows as they are processed.