adding a column to sql table using sql server management studio

Question

I have some data which is serially order by column ID. Within the data there is a column 'Ueid' which has employee numbers. A same Ueid could be present on more than one row if an employee has more than one record. To get a data for an employee serialized by time order i use below command and it works fine

SELECT ROW_NUMBER() OVER(PARTITION BY Ueid ORDER BY ID ASC) AS RowNumber, *
  FROM [dbo].[checking]
  order by ID

I want to add the column 'RowNumber' to my original data. What is an efficient way to do it?

I am thinking of using below command, but i am getting errors.

1 Error says that "the multi-part identifier "newone.[ID]" and "newone.[Row]" couldn't be bound"

2 Error says that "Invalid column name RowNumber"

with newone as (SELECT ROW_NUMBER() OVER(PARTITION BY Ueid ORDER BY ID ASC) AS RowNumber, *
  FROM [dbo].[checking]) 
update [dbo].[checking]
set Rownumber = newone.[RowNumber]
where [dbo].[checking].ID = newone.[ID]

The most efficient way to do this is don't. One of the rules of normalization is to not store calculated values. If you don't understand that sentence, I've heard good things about the book, Database Design for Mere Mortals. — Dan Bracuk
– Dan Bracuk, Commented Dec 30, 2013 at 19:42
I need to provide a copy of data to someone and they need that column. — user2543622
– user2543622, Commented Dec 30, 2013 at 19:44
Well then give them the result set of your first query. That is not a reason to alter your source data. — Zane
– Zane, Commented Dec 30, 2013 at 19:45
okay, makes sense. But I would appreciate an answer. As of now I dont know how to do it and would like to understand how to perform such tasks, if required in future. — user2543622
– user2543622, Commented Dec 30, 2013 at 19:48

Azhdeen · Accepted Answer · 2013-12-30 22:39:57Z

1

You're almost there. The syntax in your UPDATE statement is wrong.

ALTER TABLE dbo.checking ADD RowNumber INT NULL; --or not NULL, depending on requirements.

WITH newone(ID, UEID, RowNumber) AS (SELECT ID, UEID,
    ROW_NUMBER() OVER(PARTITION BY UEID ORDER BY ID ASC) AS RowNumber
    FROM [dbo].[checking]) 
UPDATE [dbo].[checking]
SET RowNumber = n.[RowNumber]
FROM dbo.checking c INNER JOIN newone n ON c.ID = n.ID;

Here's a SQL Fiddle.

answered Dec 30, 2013 at 22:39

Azhdeen

1,6561 gold badge23 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user2543622 Over a year ago

oh got it. My join statement was wrong. Is this the best method to add that data?

Azhdeen Over a year ago

That depends. If it is a one-time thing, then it is probably fine (unless your tables are ridiculously massive). If this is going to be part of a recurring process, I'd create a temp table that generates RowNumber first and then include it in the INSERT statement that populates dbo.checking. Personally, I'd probably take @DanBracuk's advice and and calculate RowNumber when I'm ready to use the data for something.

JLott · Accepted Answer · 2013-12-30 20:42:06Z

1

Alter [dbo].[checking]
Add Id_new Int Identity(1, 1)
Go

Here is a simple query to do this.

Not sure exactly what information you are trying to get into RowNumber, but to add a column you will need to use the Alter (Tablename).

The Add is where you will add the data and name the column.

Add Row_Number Int (whatever)
GO

To answer the other part of your question the I saw in the comments, you can basically copy the data from one column to another by doing:

UPDATE [dbo].[checking] 
SET Row_Number = (Name of column you are wanting to copy)

edited Dec 30, 2013 at 20:42

answered Dec 30, 2013 at 19:53

JLott

1,8384 gold badges36 silver badges57 bronze badges

7 Comments

JLott Over a year ago

I didn't understand what you were trying to do. Try that.

user2543622 Over a year ago

wont this add column which has serial number for the entire data? it will be a copy of column ID that i have in my data. But i want to put serial number within each value of UEID

Zane Over a year ago

I think you need to clarify your request a bit. This will be an identity column that will have a unique number for every row in your database.

JLott Over a year ago

The (1,1) will make the rows numbered in order which is what I assumed @user2543622 was trying to do.

Azhdeen Over a year ago

He already has a column called ID, which I would assume is an IDENTITY column already. What he needs is a new column that contains a set of numbers for each UEID that increments as ID increases in value.

|

Collectives™ on Stack Overflow

adding a column to sql table using sql server management studio

2 Answers 2

2 Comments

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related