如何增加每次 INSERT INTO 迭代的值? [英] How can I increment the value for each INSERT INTO iteration?

问题描述

我有一个查询，如下所示column1 是 int另一列是 varchar(100)

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

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

查询后的Table1

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

但是我想要

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

如何在 SQLserver 2008 StoredProcedure 中实现这一点?我一直假设查询是迭代的，他们会检查每一行的条件.但是好像聚合函数只执行一次！

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!

编辑 1

也请回答这个问题仅在完成 ​​SELECT 语句后， INSERT 就可以工作了.这就是为什么我没有得到预期的结果???我说的对吗?

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?

推荐答案

use row_number 函数给你的行序列号

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

或更安全的版本(即使您在 Table1 中没有任何行，它也能工作):

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

这篇关于如何增加每次 INSERT INTO 迭代的值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！