如何将数字插入sql中的列 [英] how to insert the numbers to column in sql
本文介绍了如何将数字插入sql中的列的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个列作为数字。
i需要在sql中向该列插入105,106,.... 193值...
解决方案
您可以为表格设置标识列。
如果您无法重新设计表格,则备用选项将返回增量编号查询。
ROW_Number
可以帮助您获得一些结果 - http://technet.microsoft.com/en-us/library/ms186734.aspx [ ^ ]。
以下是更多示例 -
使用ROW_NUMBER()分页使用SQL Server 2005和ASP.NET的数据 [ ^ ]
使用-ROW-NUMBER>在MS SQL Server 2005/2008中进行分页[ ^ ]
假设该列名为'id':
插入 进入 table1(id) values ( 105 );
while ( select max(id) from table1)< 193
begin
insert into table1(id)
选择 max(id)+ 1 来自 table1
end ;
选择 * 来自 table1
Peter Leow(solution2)向您展示了使用while
循环将值插入表的方法。我建议使用 CTE [ ^ ]作为替代方式。
; WITH MyCte AS
(
SELECT 105 AS ID
UNION ALL
SELECT ID +1 AS ID
FROM MyCte
WHERE ID< 194
)
INSERT INTO TableName(ID)
SELECT ID
FROM MyCte
i have a column as numbers.
i need to insert 105,106,....193 values to that column in sql...
解决方案
You can set an identity column for the table.
If you cannot do a table redesign, an alternate option will be to return an incremental number in the query.
ROW_Number
can help you get some results - http://technet.microsoft.com/en-us/library/ms186734.aspx[^].
Here are more examples -
Using ROW_NUMBER() to paginate your data with SQL Server 2005 and ASP.NET[^]
Paging in MS SQL Server 2005/2008 using ROW_NUMBER()[^]
let say that column is called 'id':
insert into table1 (id) values (105); while (select max(id) from table1) < 193 begin insert into table1 (id) select max(id) + 1 from table1 end; select * from table1
Peter Leow (solution2) showed you a way to insert values into table usingWhile
loop. I'd suggest to use CTE[^] as an alternative way.
;WITH MyCte AS ( SELECT 105 AS ID UNION ALL SELECT ID +1 AS ID FROM MyCte WHERE ID<194 ) INSERT INTO TableName (ID) SELECT ID FROM MyCte
这篇关于如何将数字插入sql中的列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文