?
Solved

looping SQL putting one as the line on each record

Posted on 2011-03-10
15
Medium Priority
?
268 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
[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

 
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Tammy
MySQLTuner is a script written in Perl that allows you to review a MySQL installation quickly and make adjustments to increase performance and stability. The current configuration variables and status data is retrieved and presented in a brief forma…
In this article, we will see two different methods to recover deleted data. The first option will be using the transaction log to identify the operation and restore it in a specified section of the transaction log. The second option is simpler and c…
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.
Suggested Courses

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