How can I increment the value for each INSERT INTO iteration?

Question

I have a Query as shown below column1 is int anothercolumn is varchar(100)

INSERT INTO TABLE1 (column1,column2)
SELECT (MAX(column1) FROM TABLE1)+1 ,anotherColumn FROM TABLE2

Table1 Before Query

column1  column2
-------  -------
3         test1
4         test2

Table1 After Query

column1  column2
-------  -------
3         test1
4         test2
5         anotherVal1
5         anotherVal2
5         anotherVal3

But I want

column1  column2
-------  -------
3         test1
4         test2
5         anotherVal1
6         anotherVal2
7         anotherVal3

How can I achieve this in SQLserver 2008 StoredProcedure? I always assumed that Queries are iterated and they would check the condition for each rows. But it seems like aggregate function executes only once!

EDIT 1

Please answer this too After only completing the SELECT statement the INSERT would work. Thats why I didn't get result as expected??? Am I correct?

Solution 1

use row_number function to give your rows sequential numbers

insert into Table1 (column1,column2)
select 
    (select max(column1) from Table1) + row_number() over (order by T2.anotherColumn),
    T2.anotherColumn
from Table2 as T2

or more safe version (it would work even if you don't have any rows in Table1):

insert into Table1 (column1,column2)
select 
    isnull(T1.m, 0) + row_number() over (order by T2.anotherColumn),
    T2.anotherColumn
from Table2 as T2
    outer apply (select max(column) as m from Table1) as T1

Solution 2

I know this question is answered already but maybe the solution can even be simplified further? What about

INSERT INTO TABLE1 (column1,column2)
SELECT ISNULL((SELECT MAX(column1) FROM TABLE1),0)
 +ROW_NUMBER() OVER (ORDER BY anotherColumn),
 anotherColumn FROM TABLE2

Comments

[Subin Jacob]

After only completing the SELECT statement the INSERT would work. Am I correct? Thats why I didn't get result as expected???

[Roman Pekar]

insert is working as insert of bunch of rows, and that's why your statement insert one column1 for all rows, if that's what you mean