如何在sql中的一列中插入数字 [英] How to insert number in one column in sql
本文介绍了如何在sql中的一列中插入数字的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有表emp和一列名称empmachineid。
i想要从1到最后插入数字...
喜欢
1
2
3
4
5
所有其他列都填充..
解决方案
看看例子:
DECLARE @ tmp TABLE (empName VARCHAR ( 30 ),EmpId INT NULL )
INSERT INTO @ tmp (empName)
SELECT ' A'
UNION ALL SELECT ' B'
UNION ALL SELECT ' C'
UNION 所有 SELECT ' D'
UNION ALL SELECT ' E'
UNION ALL SELECT ' F'
UNION ALL 选择 ' G'
UPDATE t1 SET t1.EmpId = t2.EmpId
FROM @ tmp AS t1 INNER JOIN (
SELECT ROW_NUMBER() OVER (< span class =code-keyword> ORDER BY empName) AS EmpID,empName
FROM @ tmp
) AS t2 ON t1.empName = t2.empName
SELECT EmpID,empName
FROM @ tmp
Re sult:
1 A
2 B
3 C
4 D
5 E
6 F
7 G
试试这段代码。
声明 @ a int
set @ a = 0
update emp
set @ a = @ a + 1,
empmachineid = 转换( varchar ( 2 ), @ a )
这有帮助吗?
CREATE TABLE [cpqa]。[tbl_NA_strFaint](
[strFaint_00] [nvarchar](MAX)
)
INSERT INTO [cpqa]。[tbl_NA_strFaint]
VALUES('UNION AL L SELECT A'),
('UNION ALL SELECT B'),
('UNION ALL SELECT C'),
('UNION ALL SELECT D')
CREATE TABLE [cpqa]。[tbl_NA_strFaintIdx](
[Idx] [int] IDENTITY(1,1),
[strFaint_00] [ nvarchar](MAX)
)
INSERT INTO [cpqa]。[tbl_NA_strFaintIdx]
SELECT [strFaint_00] FROM [ cpqa]。[tbl_NA_strFaint]
SELECT [Idx],[strFaint_00] FROM [cpqa]。[tbl_NA_strFaintIdx]
Idx strFaint_00
~~~~~~~~~~~~~~~~~~~~~~~~~~~
1 UNION ALL SELECT A
2 UNION ALL SELECT B
3 UNION ALL SELECT C
4 UNION ALL SELECT D
I have table emp and one column is there name empmachineid.
i want to insert number from 1 to end ...
like
1
2
3
4
5
all others columns are filled..
解决方案
Have a look at example:
DECLARE @tmp TABLE (empName VARCHAR(30), EmpId INT NULL) INSERT INTO @tmp (empName) SELECT 'A' UNION ALL SELECT 'B' UNION ALL SELECT 'C' UNION ALL SELECT 'D' UNION ALL SELECT 'E' UNION ALL SELECT 'F' UNION ALL SELECT 'G' UPDATE t1 SET t1.EmpId = t2.EmpId FROM @tmp AS t1 INNER JOIN ( SELECT ROW_NUMBER() OVER(ORDER BY empName) AS EmpID, empName FROM @tmp ) AS t2 ON t1.empName = t2.empName SELECT EmpID, empName FROM @tmp
Result:
1 A 2 B 3 C 4 D 5 E 6 F 7 G
Try this code.
declare @a int set @a = 0 update emp set @a = @a+1, empmachineid = Convert(varchar(2),@a)
Does this help?
CREATE TABLE [cpqa].[tbl_NA_strFaint]( [strFaint_00][nvarchar](MAX) )
INSERT INTO [cpqa].[tbl_NA_strFaint] VALUES ('UNION ALL SELECT A'), ('UNION ALL SELECT B'), ('UNION ALL SELECT C'), ('UNION ALL SELECT D')
CREATE TABLE [cpqa].[tbl_NA_strFaintIdx]( [Idx][int]IDENTITY(1,1), [strFaint_00][nvarchar](MAX) )
INSERT INTO [cpqa].[tbl_NA_strFaintIdx] SELECT [strFaint_00] FROM [cpqa].[tbl_NA_strFaint]
SELECT [Idx], [strFaint_00] FROM [cpqa].[tbl_NA_strFaintIdx]
Idx strFaint_00 ~~~~~~~~~~~~~~~~~~~~~~~~~~ 1 UNION ALL SELECT A 2 UNION ALL SELECT B 3 UNION ALL SELECT C 4 UNION ALL SELECT D
这篇关于如何在sql中的一列中插入数字的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文