Solved

looping SQL putting one as the line on each record

Posted on 2011-03-10
15
257 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 11

Expert Comment

by:JoeNuvo
Comment Utility
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
Comment Utility
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
Comment Utility
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 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 

Author Comment

by:kevin1983
Comment Utility
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
Comment Utility
angelIII: ...not just copying records
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
Comment Utility
ok - were trying to use the ROW_NUMBER() function but not having much luck so far
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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 500 total points
Comment Utility
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
Comment Utility
JoeNuvo: that did the trick, thanks
0
 

Author Comment

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

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

728 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now