Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 267
  • Last Modified:

looping SQL putting one as the line on each record

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
kevin1983
Asked:
kevin1983
  • 8
  • 3
  • 3
  • +1
1 Solution
 
AmbalalCommented:
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
 
kevin1983Author Commented:
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
 
JoeNuvoCommented:
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
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
JoeNuvoCommented:
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
 
kevin1983Author Commented:
JoeNuvo: just trying your code you suggested but currently jave incorrect syntax near CTE line 29.
any ideas?

Please clarify what CTE does?
0
 
kevin1983Author Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
kevin1983Author Commented:
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
 
kevin1983Author Commented:
angelIII: ...not just copying records
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
kevin1983Author Commented:
ok - were trying to use the ROW_NUMBER() function but not having much luck so far
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
JoeNuvoCommented:
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
 
kevin1983Author Commented:
JoeNuvo: that did the trick, thanks
0
 
kevin1983Author Commented:
Thanks Ambalal, angelIIl and JoeNuvo for all your comments on this question.
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

  • 8
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now