Solved

MS Sql Convert int column to identity conserving data

Posted on 2010-08-31
1
408 Views
Last Modified: 2012-05-10
I have a field with integers, they are not repeated and go from 1 to 1000, I need it to set it as an identity column (1,1) preserving the data it has since it is the key for a relation with another table.

This table was imported from a system that incremented this field by 1 manually.


0
Comment
Question by:robrodp
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 1

Accepted Solution

by:
zapacila89 earned 500 total points
ID: 33572860
If you have access to enterprise manager or SSMS, you can browse to the table, right click => design.

In the definition of the table, select the column, set identity on and seed value to the last existing value in the table. Do save.

More info on the steps

Highlight the column (actually a row in this presentation) you are interested in by left clicking the gray square to the left of the column name.
With the row highlighted the column detail will be shown in the bottom half of the window with all the things you need.

Next record inserted into to the table will have the next identity column auto incremented.

(In SSMS 2005 you simple change the column to be as identity same from the design window (rigt-clik on table name an click design) and clicking on the column in the bottom will appear a table of properties which contains Identity specification. In SSMS 2005 it will automatically pick from the next number)
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Format Date fields 11 65
SP result not being displayed 5 69
T-SQL: problem comparing datetime 4 83
Create a Calendar table 29 45
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question