Solved

MS Sql Convert int column to identity conserving data

Posted on 2010-08-31
1
412 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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

617 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