How to insert number in one column in sql

Question

1.00/5 (2 votes)

See more:

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..

Posted 18-Aug-13 18:21pm

Nawab Ahmad

Add a Solution

Comments

PIEBALDconsult 19-Aug-13 0:47am

Why?

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2013-08-19T01:25:00

Solution 2

Have a look at example:

SQL

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

Posted 19-Aug-13 1:25am

Maciej Los

Updated 19-Aug-13 9:26am

v2

Comments

_Asif_ 19-Aug-13 7:30am

This is not a good approach. Arun approach is better

Maciej Los 19-Aug-13 7:33am

Could you exaplain me, why?

_Asif_ 19-Aug-13 9:13am

This approach is more resource intensive, first a temporary table, then a select with unions, then one more select finally leading to an update! No need to use a tank if a problem can be killed by a hammer.

Maciej Los 19-Aug-13 9:29am

:laugh:
Man, it is an example! I had created temporary table with names of employees to show how to add column with numbers 1, 2, 3 "to the end", then i used ROW_NUMBER() function to create id's.

Oh Gosh...

_Asif_ 20-Aug-13 0:20am

Well, in that case it should have been written as "comments" :)

ArunRajendra · Answer 2 · 2013-08-18T18:27:00

Solution 1

Try this code.

SQL

declare @a int
set @a =  0


update emp
set  @a =  @a+1,
empmachineid  = Convert(varchar(2),@a)

Posted 18-Aug-13 18:27pm

ArunRajendra

Updated 18-Aug-13 18:51pm

v2

Comments

Nawab Ahmad 19-Aug-13 0:40am

Hi,

Msg 425, Level 16, State 1, Line 4
Data type int of receiving variable is not equal to the data type nvarchar of column 'empmachineid'.

ArunRajendra 19-Aug-13 0:52am

Try the modified code. Modify Varchar(2) to as per your field length.

RedDk · Answer 3 · 2013-08-19T07:36:00

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

How to insert number in one column in sql

3 solutions

Solution 2

Solution 1

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us