Move block of data SQL

Leif Nyström 21

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

Olaf Helper 44,501 Reputation points

2020-12-04T11:41:47.2+00:00

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.
Erland Sommarskog 111.1K Reputation points MVP

2020-12-04T12:59:19.817+00:00

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.
Leif Nyström 21 Reputation points

2020-12-04T18:50:37.86+00:00
I am sorry a part of the code did not come with my question.

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

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

Leif Nyström 21

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

Leif Nyström 21 Reputation points

2020-12-04T18:58:46.84+00:00

This is the table I am using.
create table population (id int, regiod int, locaid int, popMen int, popWoman int, age int)
Leif Nyström 21 Reputation points

2020-12-04T21:48:20.733+00:00

SQL server 2017

Accepted answer

Viorel 117.2K Reputation points

2020-12-04T19:37:31.007+00:00
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).
Please sign in to rate this answer.
Leif Nyström 21 Reputation points

2020-12-04T21:46:17.19+00:00

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.

Erland Sommarskog 111.1K Reputation points MVP

2020-12-04T22:20:53.54+00:00

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
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Move block of data SQL

0 additional answers

Your answer