<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Article Discussions
»
Article Discussions by Author
»
Discuss content posted by Thomas Pieries
»
Changing a Non-IDENTITY column to IDENTITY...
28 posts, Page 2 of 3
««
1
2
3
»»
Changing a Non-IDENTITY column to IDENTITY and vice versa
Rate Topic
Display Mode
Topic Options
Author
Message
the sqlist
the sqlist
Posted Friday, April 10, 2009 8:07 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Today @ 8:18 AM
Points: 124,
Visits: 471
SQL Frenzy (2/25/2008)
Hi,
Can we have two identity columns on a single table?
If not then how can one insert an identity column in a table having an identity column?
Regards,
Ahmad
The answer is simple: you can only have 1 identity column per table. If what you want with the second part of your question is to move the identity attribute from one column to the other, personally I don't see why one would do such a thing as you already have an identity, then first you have to turn off the identity on your original column and then add another column with identity attribute.
As per article, the best way to go is in the Enterprise Manager or Management Studio in design mode on the table, switch on the identity attribute on the column, copy the SQL that was generated and then save it in a file.
Don't just give the hungry man a fish, teach him how to catch it as well.
the sqlist
Post #694845
WILLIAM MITCHELL
WILLIAM MITCHELL
Posted Friday, April 10, 2009 9:15 AM
UDP Broadcaster
Group: General Forum Members
Last Login: Tuesday, May 14, 2013 6:45 AM
Points: 1,441,
Visits: 2,485
I have to agree with the previous posters, using the GUI to generate the change script is the best approach. The column being changed stays in the same ordinal position, the script runs inside a transaction, and preserves all the indexes, constraints etc.
You can't have users in the db when you're making this type of change, so you can't avoid having at least some downtime.
Post #694909
ChiragNS
ChiragNS
Posted Monday, April 13, 2009 2:00 AM
SSCrazy
Group: General Forum Members
Last Login: Wednesday, April 24, 2013 5:02 AM
Points: 2,365,
Visits: 1,825
Good tip.
"Keep Trying"
Post #695584
Kavin-1064454
Kavin-1064454
Posted Monday, April 13, 2009 7:36 AM
SSC Journeyman
Group: General Forum Members
Last Login: Monday, May 31, 2010 11:20 PM
Points: 86,
Visits: 74
Thank you very much Mr.Thomas. I came across the same issue ie non- identity to identity type change and your article has helped a me a lot.
But when I have tried it in a temp database it shows the error
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'TMP' to data type int.
But again I have tried in another database its working fine.
--drop table tmp
create table tmp
(s_no int,
name varchar(10)
)
insert into tmp
values
(12,'name1')
insert into tmp
values
(13,'name2')
insert into tmp
values
(14,'name3')
ALTER TABLE tmp ADD s_no_II INT IDENTITY(1,1)
ALTER TABLE tmp DROP COLUMN s_no
EXEC sp_rename
'tmp.s_no_II','s_no', 'COLUMN'
Can u brief and how the error can be overcome?
Thanks in advance.
Post #695790
JeremyG
JeremyG
Posted Tuesday, April 14, 2009 2:31 PM
SSC Rookie
Group: General Forum Members
Last Login: Sunday, December 11, 2011 10:58 AM
Points: 49,
Visits: 100
I have to respectfully disagree with the posters that are saying that using the GUI is the best approach. If by best you mean easiest, then that would be true. But if you look at what is involved, the article really dives into an interesting and super-efficient way to accomplish the task. If you had a 120gb table (as I have had), and you do this through the GUI (or script what the GUI would do and run it), you would find your users very displeased at the amount of downtime, assuming you dont run out of diskspace while your query creates a copy of the table with a new identity column. This is what the GUI does, if you wanted to add an identity to a table without one (in 2005)...
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_SourceTable
(
Id int NOT NULL IDENTITY (1, 1),
Column1 varchar(50) NULL,
Column2 varchar(50) NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT dbo.Tmp_SourceTable OFF
GO
IF EXISTS(SELECT * FROM dbo.SourceTable)
EXEC('INSERT INTO dbo.Tmp_SourceTable (Column1, Column2)
SELECT Column1, Column2 FROM dbo.SourceTable WITH (HOLDLOCK TABLOCKX)')
GO
DROP TABLE dbo.SourceTable
GO
EXECUTE sp_rename N'dbo.Tmp_SourceTable', N'SourceTable', 'OBJECT'
GO
COMMIT
If i didnt have another 120gb on my disk, i would not be able to do the above based on diskspace alone.
Very nice and very interesting article.
Post #697035
Jeremy Giaco
Jeremy Giaco
Posted Tuesday, April 14, 2009 2:57 PM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Tuesday, August 04, 2009 8:25 AM
Points: 159,
Visits: 122
...but then again, assuming that there are no real concerns (small table, dev database, etc.) with using the "create destination, load destination, drop source, and rename destination" table approach, I'd probably use the GUI as well.
Post #697068
keycard
keycard
Posted Friday, May 08, 2009 5:11 PM
SSC Veteran
Group: General Forum Members
Last Login: Friday, April 19, 2013 5:25 PM
Points: 217,
Visits: 76
If you batch your delete and output it into the destination table, you don't have to set identity_insert
on. At least thats what I'm seeing. I did put in a post about the OUTPUT function asking if it sets the identity_insert of a table to on or not. I've not heard anything yet. Over all, I thought this post was very useful.
Thanks
JN
Post #713424
the sqlist
the sqlist
Posted Saturday, May 09, 2009 8:57 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Today @ 8:18 AM
Points: 124,
Visits: 471
jeremygiaco (4/14/2009)
I have to respectfully disagree with the posters that are saying that using the GUI is the best approach. If by best you mean easiest, then that would be true. But if you look at what is involved, the article really dives into an interesting and super-efficient way to accomplish the task. If you had a 120gb table (as I have had), and you do this through the GUI (or script what the GUI would do and run it), you would find your users very displeased at the amount of downtime, assuming you dont run out of diskspace while your query creates a copy of the table with a new identity column. This is what the GUI does, if you wanted to add an identity to a table without one (in 2005)...
The problem is that there is no other way of doing it and the article actually does the same thing, copies the original table to a new table. Even when you do a simple ALTER TABLE ADD ... IDENTITY in the background happens the same thing, copy the table, and that is why the GUI generating script does the same.
If you really know a different way I would like to see it.
Don't just give the hungry man a fish, teach him how to catch it as well.
the sqlist
Post #713545
JeremyG
JeremyG
Posted Saturday, May 09, 2009 10:56 AM
SSC Rookie
Group: General Forum Members
Last Login: Sunday, December 11, 2011 10:58 AM
Points: 49,
Visits: 100
I will probably continue to use the create table/copy data/rename tables approach 99% of the time myself..but i think the article does offer a different way to do this..the code was:
CREATE TABLE DESTINATION_TB( ID INT IDENTITY(125006,1) NOT NULL ,DATE DATETIME ,COST MONEY)
ALTER TABLE SOURCE_TB SWITCH TO DESTINATION_TB
DROP TABLE SOURCE_TB
EXEC sp_rename 'DESTINATION_TB' ,'SOURCE_TB'
If I understand his logic correctly, it's not quite the same thing since you're only changing the pointers, so you never really have to copy all that data. I would say that the IO stats speak for themself in this case.
Post #713575
WILLIAM MITCHELL
WILLIAM MITCHELL
Posted Saturday, May 09, 2009 10:59 AM
UDP Broadcaster
Group: General Forum Members
Last Login: Tuesday, May 14, 2013 6:45 AM
Points: 1,441,
Visits: 2,485
the sqlist (5/9/2009)
jeremygiaco (4/14/2009)
I have to respectfully disagree with the posters that are saying that using the GUI is the best approach. If by best you mean easiest, then that would be true. But if you look at what is involved, the article really dives into an interesting and super-efficient way to accomplish the task. If you had a 120gb table (as I have had), and you do this through the GUI (or script what the GUI would do and run it), you would find your users very displeased at the amount of downtime, assuming you dont run out of diskspace while your query creates a copy of the table with a new identity column. This is what the GUI does, if you wanted to add an identity to a table without one (in 2005)...
The problem is that there is no other way of doing it and the article actually does the same thing, copies the original table to a new table. Even when you do a simple ALTER TABLE ADD ... IDENTITY in the background happens the same thing, copy the table, and that is why the GUI generating script does the same.
If you really know a different way I would like to see it.
As the author stated, there are many tasks to get the SWITCH to work . . .
* Drop all the Foreign Keys referencing to SOURCE_TB if any
* Alter all the objects with schemabinding if any
* Drop all the Indexes from SOURCE_TB
* Create the new table DESTINATION_TB with same schema as the SOURCE_TB
* SWITCH the data between the tables
* Drop the table Source_TB
* Rename the table Destination_TB to Sourse_TB
* Recreate all the Foreign Keys to refer to SOURCE_TB
* Recreate all the Indexes on SOURCE_TB
* Recreate functions, views with SCHEMABINDING
* Create all other constraints
. . . and the GUI will generate all of those changes for you, and works regardless of which edition you're using.
I would also like to point out that if you run out of disk space whilst copying a 120 GB table then you need a much larger disk, because you certainly don't have enough space for your database & log backups.
Post #713576
« Prev Topic
|
Next Topic »
28 posts, Page 2 of 3
««
1
2
3
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
