November 16, 2015 at 4:07 pm
I need to make an already existing field an identity field. Currently, it's just an integer field, but it does contain all unique values. The database is already populated, and I can't risk losing any data. Can someone point me in the right direction? Thank you so much.
November 16, 2015 at 4:51 pm
You can try changing the column to an identity column under Design in SMS. If that works, you may have to seed the table with the correct next identity value. No guarantees with this.
Another way that I know will work is to:
1. Create an identical table.
2. Change the column to identity
3. Set Identity_Insert ON for the new table
4. Copy all the data over from the old table to the new table
5. Set Identity_Inset OFF for the table
6. Test the new table
7. Delete the old table
8. Rename the new table.
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
November 17, 2015 at 7:29 am
That's the problem I'm having - I'm used to working with MySQL, where you CAN just add an identity field, auto-numbering field, etc., after the table has been created and all the data exists in it - it's much more flexible that way. I only use SQL Server for 1 project, and I haven't touched it since last year. 🙂
I've now created an exact duplicate of the table, with no data (of course), but I'm not sure how to get the data into the table. Also, where do I "Set Identity_Insert ON for the new table." It's the procedure of your steps I'm not sure of.
I created a query and used the following, but I'm getting an error:
SET IDENTITY_INSERT dbo.tblApplicants222 ON;
INSERT INTO tblApplicants222 SELECT * FROM tblApplicants;
The error is: Msg 8101, Level 16, State 1, Line 3
An explicit value for the identity column in table 'tblApplicants222' can only be specified when a column list is used and IDENTITY_INSERT is ON.
Thank you!
November 17, 2015 at 7:34 am
Can the identity be inserted randomly against the existing data? Or does the data have to follow an existing order for the identity?
If the existing column denotes the order, then you will need to create a new table and copy the data over and rename.
If the existing data can have any random identity value, you can simply add in a new column with identity specification, drop the old column, rename the new column.
The only thing with that is the new identity column will be at the end of the table, not a problem for some, but I have seen people get picky where columns are in the column outputs.
November 17, 2015 at 7:48 am
Anthony, it can't be random numbers. There are other tables that use this ID. Here's the thing ... there IS a table on the server that is an identity column, and does increment automatically. When I brought the table down from the server, using the Tasks >> Export Data option, the table lost it's identity. Now I need to bring it back. Maybe there's a better way to get the table from the server and NOT lose the identity? Please be specific ... I don't know SQL Server that well. 🙂
Thank you.
November 17, 2015 at 7:58 am
Okay ... I think I figured it out. Thank you everybody for your input. I found a tutorial online on how to copy data from one table to another, and it worked .. finally! Thank you, everyone.
November 17, 2015 at 8:03 am
If the current column has none unique values in the range then it can't become the identity unless you manually force the identity_insert value to on.
I would create the table on the destination as required and re-run your export data run again.
Alternatively as LinksUp has already stated
Create a second table with identity specification
set identity_insert <<second table>> on
insert into <<second table>> (column list) select * from <<first table>>
set identity_insert <<second table>> off
rename <<first table>> to <<old first table>>
rename <<second table>> to <<first table>>
That way you can insert values which are duplicated into the identity column.
November 17, 2015 at 8:05 am
sandy-808981 (11/17/2015)
Okay ... I think I figured it out. Thank you everybody for your input. I found a tutorial online on how to copy data from one table to another, and it worked .. finally! Thank you, everyone.
Care to post the tutorial for those who may need the same help?
November 17, 2015 at 3:42 pm
Of course! Here is the tutorial.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply