[Last Call] Learn how to a build a cloud-first strategyRegister Now

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

looping stored procedure

hi i have a table A having records 1 to 10. i need to create a stored procedure that loops through each of the recs of A and multiply with previous row value and inserts to a new table B
 say table A
1
2
3
4
5
then after running procedure table b should be with val

1    1
2    2
3    6
4   12

ie eg: 3 * 2 =6 2 is the val of prev rec

0
AnnaJames77
Asked:
AnnaJames77
  • 3
  • 2
  • 2
  • +2
3 Solutions
 
kamindaCommented:
If the values in sequence order without gaps you can use simple query like this

INSERT INOT tableB (column1, column2)
SELECT columna, columna * (columna - 1) from tableA
0
 
kamindaCommented:
sorry there is a typo in query

INSERT INTO tableB (column1, column2)
SELECT columna, columna * (columna - 1) FROM tableA
0
 
AnnaJames77Author Commented:
no its not sequential so how can i
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
x-menIT super heroCommented:
use a CURSOR to iterate through the table, saving the previous value on a variable.
0
 
AnnaJames77Author Commented:
please can you explain it. iam new to this
0
 
x-menIT super heroCommented:
declare @mult table (C1 int, C2 int)
declare @val1 int, @val2 int
declare iterate cursor for select x from lab
open iterate
Set @val1 = Null
fetch next from iterate into @val2
WHILE @@FETCH_STATUS = 0
begin
      If @val1 is null
            insert into @mult VALUES (@val2,@val2)
      Else      
            insert into @mult VALUES (@val2,@val2*@val1)
            
      Set @val1 = @val2
      fetch next from iterate into @val2
end
select * from @mult
0
 
x-menIT super heroCommented:
consider [lab] your tableA and @mult your tableB
0
 
anillucky31Commented:
I have one question on this. How you will determine that what is previous row for current record.
Firstly you need to sort the records on the basis of some column, then  your rows will be in some order.

I am assuming that you have sorted rows on some column name.

IN this example i am sorting ROWS ON COLUMN ID. You also have TO sort ON SOME COLUMN NAME

IF you dont want TO sort THEN just remove ORDER BY IN my example. I have attached my code sample
DECLARE @Id int 
 DECLARE @Value INT
 DECLARE @PreviousValue INT
 DECLARE @NewValue int
 
 SET @PreviousValue = 1

DECLARE somecursor CURSOR FOR SELECT Id, Value FROM TableA ORDER BY id asc
OPEN somecursor
FETCH NEXT FROM somecursor INTO @Id, @Value

WHILE @@FETCH_STATUS = 0
BEGIN

SET @NewValue = @Value * @PreviousValue

INSERT INTO TableB (Id, Value)
VALUES(@Id, @NewValue)

SET @PreviousValue = @Value


FETCH NEXT FROM somecursor INTO @Id, @Value
END

CLOSE somecursor
DEALLOCATE somecursor

SELECT * FROM TableB

Open in new window

0
 
KrtyknmCommented:
I have just made it thru common table expression (CTE) , since it will be a set based approach the execution will be very fast.
WITH CTE
AS
(
	SELECT 
		ROW_NUMBER() OVER (ORDER BY id) [RowNum],
		[id]
	FROM dbo.tblA

)

INSERT INTO dbo.[tblB] (id,Cartesianid)
SELECT  
	[id],
	[id]*[id] [Cartesianid]
FROM CTE AS A
WHERE [RowNum] = 1
UNION ALL
SELECT 
	A.[id]
	,A.[id] * B.[id] [Cartesianid]
FROM dbo.tblA AS A
INNER JOIN  dbo.tblA AS B
ON A.[id] = B.[id] + 1

Open in new window

0
 
KrtyknmCommented:
Sorry, I have attached the wrong one, the below query will bring the exact values as you expect
WITH CTE
AS
(
	SELECT 
		ROW_NUMBER() OVER (ORDER BY id) [RowNum],
		[id]
	FROM dbo.tblA

)
INSERT INTO dbo.[tblB] (id,Cartesianid)
SELECT  
	[id],
	[id]*[id] [Cartesianid]
FROM CTE AS A
WHERE [RowNum] = 1
UNION ALL
SELECT 
	 A.[id]
	,A.[id] * B.[id] [Cartesianid]
FROM CTE AS A
INNER JOIN CTE AS B
ON A.[RowNum] = B.[RowNum] + 1

Open in new window

0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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

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