question

LeifNystrm-1147 avatar image
0 Votes"
LeifNystrm-1147 asked ErlandSommarskog commented

Move block of data SQL

I have a problem finding a good way of moving data in a table without using a while loop.
I need to move the data in the table one step ahead. See my SQL code. Looping trough the data with a while loop is too slow a process.
My question is, is it possible to move a block of data instead?

 DECLARE @Counter int,  @popMen float, @CountLocal int, @maxLocal int
    
 SET @Counter=110
 SET @CountLocal=1
 SELECT @maxLocal = Max(locaid) From population where (regiod=1)
 WHILE (@CountLocal <= @maxLocal)
     BEGIN
         WHILE (@Counter >=0)
         BEGIN
             SELECT @popMen = popMen 
             from population where (regiod=1 AND locaid=@CountLocal AND age=@Counter)
             SET @Counter=@Counter-1
             SET @popMen=0
         END
         SET @CountLocal = @CountLocal + 1
         SET @Counter=110
     END


sql-server-generalsql-server-transact-sql
· 6
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

What exactly do you mean with "Move block of data SQL"?

Your SQL code is doing in end-effect nothing then select a value .. and to ignore it.

0 Votes 0 ·

I am sorry a part of the code did not come with my question.



DECLARE @Counter int, @popMen float, @popWoman float, @CountLocal int, @maxLocal int
SET @Counter=109
SET @CountLocal=1
SELECT @maxLocal = Max(locaid) From population where (regiod=1)
WHILE (@CountLocal <= @maxLocal)
BEGIN
WHILE (@Counter >=0)
BEGIN
SELECT @popMen = popMen, @popWoman=popWoman
from population where (regiod=1 AND locaid=@CountLocal AND age=@Counter)
update population set popMen=@popMen, popWoman=@popWoman where (regiod=1 And locaid=@countLocal and age=(@Counter + 1))
SET @Counter=@Counter-1

         END
         SET @CountLocal = @CountLocal + 1
         SET @Counter=109
     END








0 Votes 0 ·

The answer to your question is, yes, you can. But that is also all I can say, since it is not clear from your post what you want to do. As Olaf says, all your code does is to retrieve one value at time from a table only to discard it.

For this type of question, we often recommend that you post CREATE TABLE statements for your table(s) together with INSERT statements with sample data, enough to illustrate all angles of the problem, and then the expected results given the sample. This helps to clarify what you are asking for and makes it easy to copy and paste into a query window so that we can develop a tested solution. Also, state which version of SQL Server you are suing, so we don't suggest a feature you cannot use.

0 Votes 0 ·

Hi, my code was wrong. This how it should work.

 DECLARE @Counter int,  @popMen float, @popWoman float, @CountLocal int, @maxLocal int
    
 SET @Counter=109
 SET @CountLocal=1
 SELECT @maxLocal = Max(locaid) From population where (regiod=1)
 WHILE (@CountLocal <= @maxLocal)
     BEGIN
         WHILE (@Counter >=0)
         BEGIN
             SELECT @popMen = popMen, @popWoman = popWoman 
             from population where (regiod=1 AND locaid=@CountLocal AND age=@Counter)
             update population set popMen=@popMen, popWoman=@popWoman where (regiod=1 And locaid=@countLocal and age=(@Counter + 1))
             SET @Counter=@Counter-1
                
         END
         SET @CountLocal = @CountLocal + 1
         SET @Counter=109
     END


0 Votes 0 ·

This is the table I am using.
create table population (id int, regiod int, locaid int, popMen int, popWoman int, age int)

0 Votes 0 ·

SQL server 2017

0 Votes 0 ·

1 Answer

Viorel-1 avatar image
0 Votes"
Viorel-1 answered ErlandSommarskog commented

Check if the next statement performs the same things:

 update p1
 set p1.popMen = p2.popMen,
     p1.popWoman = p2.popWoman
 from population p1
 inner join population p2 on p2.regiod = p1.regiod and p2.locaid = p1.locaid and p2.age = p1.age - 1
 where p1.regiod = 1

Or maybe execute ‘update population set age += 1’, remove the last row (age 111) and insert the first one (age 0), or replace the whole last row (within groups).

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you for your kind answers!

The first idea i will consider and think through.

The second one works but is a little bit rough, I feel that it's insecure especially when we are talking about over 61000 datapoints.
37 regions with average 15 communities and age from 0-110 years/
I also need to be able to keep track of the ID.

0 Votes 0 ·

Or maybe execute ‘update population set age += 1’, remove the last row (age 111) and insert the first one (age 0), or replace the whole last row (within groups).

Sounds like the winner to me. Leif did not post any sample data, but it seems that like this is what he is doing. That is, he is not really moving a data block, he is simply making people one year older. Although in Viorel's statement the WHERE clause on regoid is missing.

I think the full thing would be like this:

 BEGIN TRANSACTION
 DELETE population WHERE age = 110 AND regoid = 1
 UPDATE population SET age +=1 WHERE regoid = 1
 INSERT for age 0?
 COMMIT TRANSACTION



1 Vote 1 ·