Solved

looping SQL putting one as the line on each record

Posted on 2011-03-10
15
258 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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 142

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 142

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 142

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 500 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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

In this article I will describe the Backup & Restore 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.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…

813 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

17 Experts available now in Live!

Get 1:1 Help Now