Improve company productivity with a Business Account.Sign Up

x
?
Solved

looping SQL putting one as the line on each record

Posted on 2011-03-10
15
Medium Priority
?
269 Views
Last Modified: 2012-05-11
Hi,

We have the below SQL code in MS SQL management studio 2005, What I am trying to do is use a line number starting at 1 and incrementing by 1 until it reaches the last record in a SQL view (named: view_WKLYFORECAST_PART)
But what it is doing is looping and puts one as the line on each record for all 4320 records then starts again and puts line 2 on all records, then 3 etc so I think I have got my start and stops all wrong.

When I try to run the code it takes a long time, i then stop after a minute ans it says its updates some records as below:, any suggestion on whats wrong with my code?.

(4320 row(s) affected)

(4320 row(s) affected)

(4320 row(s) affected)

(4320 row(s) affected)

(4320 row(s) affected)

(4320 row(s) affected)

(4320 row(s) affected)

(4320 row(s) affected)

(4320 row(s) affected)

(4320 row(s) affected)

(4320 row(s) affected)

(4320 row(s) affected)

Thanks, kevin
declare @line as int

set @line = 1

While @line <= (select Count(partCode) from view_WKLYFORECAST_PART)
begin 

Insert into [@FORECASTDETAIL]
Select 10 as absID,
@line  as Code,
@line as name,
PartCode as 'item',
Convert (Nvarchar(10),(WeekStart),126) as Date,
TotalForecastQty as Qty
From view_WKLYFORECAST_PART
set @line = (@line +1)
End

Open in new window

0
Comment
Question by:kevin1983
  • 8
  • 3
  • 3
  • +1
15 Comments
 
LVL 2

Expert Comment

by:Ambalal
ID: 35093361
1. Where clause is missing in last view_WKLYFORECAST_PART select statement.
2. Add proper where clause which should select only one record at a time and then insert that record.
    This should solve your problem

 
0
 

Author Comment

by:kevin1983
ID: 35093380
Thanks, but please can you clarify exactly where the "where" clause needs to go and does something need to come after the where clasue like where =?
0
 
LVL 11

Expert Comment

by:JoeNuvo
ID: 35093383
if you try to put something like "line number" for all records from your view.

maybe this is the code you should try using ROW_NUMBER() function


;WITH CTE AS (SELECT partCode, WeekStart, TotalForecastQty, ROW_NUMBER() OVER (ORDER BY WeekStart) as Line FROM view_WKLYFORECAST_PART)
INSERT INTO @FORECASTDETAIL
SELECT
	10 as absID,
	Line  as Code,
	Line as name,
	PartCode as 'item',
	Convert (Nvarchar(10),(WeekStart),126) as Date,
	TotalForecastQty as Qty
From CTE

Open in new window

0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LVL 11

Expert Comment

by:JoeNuvo
ID: 35093405
what Ambalal try to say is.
you should need to do something with @line inside your while loop


for ex



while ....
begin
      insert into @...
      select ......
      ....
      FROM view_WKLYFORECAST_PART
      WHERE something = @line  <-- this is what your code is lacking.

      ....
end
0
 

Author Comment

by:kevin1983
ID: 35093483
JoeNuvo: just trying your code you suggested but currently jave incorrect syntax near CTE line 29.
any ideas?

Please clarify what CTE does?
0
 

Author Comment

by:kevin1983
ID: 35093490
JoeNuvo:You said
WHERE something = @line  <-- this is what your code is lacking.
but after the WHERE clause we dont know what to put for "Something"?

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35093513
can you please clarify what exactly you are trying to achieve?
if it's just "copying" all records from a view to a table, you need exactly 1 INSERT ... SELECT statement.
if it's a more complex process, please clarify
0
 

Author Comment

by:kevin1983
ID: 35093564
angelIII: yes - We are trying to number the records as they are transferred into the table: "FORECASTDETAIL" so that each record has a new line number as the line number is the primary key and needs to be unique.
0
 

Author Comment

by:kevin1983
ID: 35093568
angelIII: ...not just copying records
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35093575
so, why not have the field in the destination table to be identity?
that would do the job for you automatically?

otherwise, you could use ROW_NUMBER() function in the SELECT query. ...
0
 

Author Comment

by:kevin1983
ID: 35093792
ok - were trying to use the ROW_NUMBER() function but not having much luck so far
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35093837
a more explicit response could help to solve the issues you have.
as I wrote above: you need to clarify, as we are not standing behind you ..

presuming you are using sql 2005+ AND the database being in compatibility mode 90 or higher.


;WITH DATA AS (Select 10 as absID,
ROW_NUMBER() OVER( ORDER BY newid()) as Code,
PartCode as 'item',
Convert (Nvarchar(10),(WeekStart),126) as Date,
TotalForecastQty as Qty
From view_WKLYFORECAST_PART
)
Insert into [@FORECASTDETAIL]
SELECT absID, Code, Code as Name
  Item, Date, Qty
 FROM DATA 

Open in new window


and nothing else, no loop etc...
but this has basically been suggested by JoeNuvo above
0
 
LVL 11

Accepted Solution

by:
JoeNuvo earned 2000 total points
ID: 35094310
if you just want "unique" number
as suggest by angelIII, IDENTITY is better way to go for

look into your code, there should have something like
Declare @FORECASTDETAIL TABLE (absID int, code int, name int, ... )

modify part of "code" to be
Declare @FORECASTDETAIL TABLE (absID int, code int IDENTITY (1,1), name int, ... )

then for whole of your given code
change to below code.

INSERT INTO @FORECASTDETAIL (absID, [item], [Date], Qty)
SELECT 10, PartCode, Convert (Nvarchar(10),(WeekStart),126), TotalForecastQty
FROM view_WKLYFORECAST_PART

UPDATE @FORECASTDETAIL
SET [name] = code

Open in new window


0
 

Author Closing Comment

by:kevin1983
ID: 35164385
JoeNuvo: that did the trick, thanks
0
 

Author Comment

by:kevin1983
ID: 35164392
Thanks Ambalal, angelIIl and JoeNuvo for all your comments on this question.
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
An introductory discussion about Oracle Analytic Functions which are used to calculate or compute Aggregate values, based on a group of rows.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

589 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question