0

I have an existing table named Employee and have to add a new column Employee_Id. EmployeeId should be 10 digit unique number and always have to start with 10.

For example 

1000000000
1000000001
1023456789

So I need to add bunch of unique 10 digit Id's to an existing table which already has data.

Can anyone help me sort this out.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user1078389
  • 23
  • 2

4 Answers4

4
SELECT 1000000000 + ROW_NUMBER() OVER( ORDER BY YourColumn ) AS 'rownumber',*
    FROM Employee

This will get the data from the existing table, with an extra column for the new id.

Rob
  • 357
  • 1
  • 3
  • 13
2

If you are using SQL Server, IDENTITY would help you.

http://msdn.microsoft.com/en-us/library/ms186775.aspx

Update: You should see the discussion here in order to alter table.

Adding an identity to an existing column

Community
  • 1
  • 1
Junichi Ito
  • 2,438
  • 1
  • 23
  • 46
  • it's not going to help with the existing data unless whole of it is reinserted right? – naresh Jan 18 '12 at 02:39
  • Then, similar discussion is here. How about this? http://stackoverflow.com/questions/1049210/adding-an-identity-to-an-existing-column – Junichi Ito Jan 18 '12 at 02:54
  • but that is in case if you already have one, the OP didn't mention that, @user1078389 do you already have a primary key? – Hector Sanchez Jan 18 '12 at 03:02
2

If you already have and integer PK, you may add a calculated column (persisted or not). It value will be PK + 1000000000, provided no pk is bigger than 1000000000.

user1154664
  • 1,392
  • 13
  • 11
1
ALTER TABLE YOURTABLE
ADD COLUMNNAME INTEGER IDENTITY(1000000000, 1)

It will not generate all with 10.. but it will generate al the possibilities, if it get passed you will get 11.. but that would be a lot of numbers to reach that.

Hope it helps

Hector Sanchez
  • 2,297
  • 4
  • 26
  • 39