Change PK from uniqueidentifier to identity

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Change PK from uniqueidentifier to identity

Author

Topic

mikebird
Aged Yak Warrior

529 Posts

Posted - 2008-03-27 : 09:30:58

I've used a table with a uniqueidentifier for a long time, but all I need is an identity.

1. When I use Design Table to set the data type to anything else I'm told it can't be converted to. I don't want to convert from an encrypted uniqueidentifier! I just want to change the table def to have a smallint there. What's the problem?

2. I try ALTER TABLE... <the previous definition> with smallint there instead but I get a weird 'syntax incorrect near '('' which seems to arise whenever using ALTER TABLE...?

I read the SQLteam section on identity, but not sure if I need to put it in the table def or the insert statements. Will you give me an example?

Thanks

X002548
Not Just a Number

15586 Posts

Posted - 2008-03-27 : 09:33:07

The DDL would be helpful

What the Unique Identifier used for?

Is it a parent of other tables?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

mikebird
Aged Yak Warrior

529 Posts

Posted - 2008-03-27 : 09:41:26

This is a really simple table, with no parent or child. The first time I made it, I only knew you could have that unique encrypted newid() thing. All I need is a column to reference the table on 1,2,3,4,5... and to swap this for what's there now. I'll truncate it first - there's nothing in there of value

Imukai
Starting Member

29 Posts

Posted - 2008-03-27 : 09:55:51

If you're going to truncate the table anyway, I'd say just Delete that uniqueid column and insert a new column (INT) and mark it as an identity field in the properties below.

mikebird
Aged Yak Warrior

529 Posts

Posted - 2008-03-27 : 10:26:09

Imukai

This is new territory! No idea you could remove or add a column. I thought it was all in CREATE TABLE all in one go and not piece by piece. Can you add & delete bits & pieces of a stored procedure too?

This might be where I was getting the error before. By editing the table script to have ALTER instead of CREATE, can't I change anything I like and leave the rest? I see you can do DROP & ADD commands within the ALTER

What's the syntax for the column removal / addition?

Thanks!

nr
SQLTeam MVY

12543 Posts

Posted - 2008-03-27 : 10:42:53

alter table mytbl drop column guid
alter table mytbl add id int identity(1,1)

or in full
alter table mytbl drop constraint pk
alter table mytbl drop column guid
alter table mytbl add id int identity (1,1) not null
alter table mytbl add constraint pk primary key (id)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Imukai
Starting Member

29 Posts

Posted - 2008-03-27 : 10:45:48

The "easiest" way that I know to do it, since you mentioned in the original post that you have access to Design Table mode, is to use that.

Right click the unique column you have, and Delete Column. Then, where you want the new column to be, right click and choose Insert Column (or put it at the bottom of the other columns if you want it to be there).

Give it a name, set its type to Int, and down below amongst the properties you will see Identity. Change it from No to Yes.

If you want this to be your new PK, while you are still on this column click the little key icon along the toolbar at the top.

Save the changes and all you have left to do is truncate it if you want to start fresh.

Incidentally, if you don't truncate it, as soon as you Save the table from adding that Identity column, it will go ahead and backfill all rows with an identity.. so if you wanted to keep the data you have, you could.

or if you prefer ALTER.. use NR's commands. :)

mikebird
Aged Yak Warrior

529 Posts

Posted - 2008-03-27 : 11:07:53

How do I populate the smallint PK now?

select newid(), etc........ is redundant

What's the syntax for putting a number in there? I know it's IDENTITY, but dunno if it wants () with some parameters. This doesn't like brackets now...

mikebird
Aged Yak Warrior

529 Posts

Posted - 2008-03-27 : 11:10:34

Should IDENTITY (1,1) be in the column def?

Imukai
Starting Member

29 Posts

Posted - 2008-03-27 : 11:12:35

You don't have to populate it - it automatically increments itself as new rows are added. The (1,1) tells it to start at 1 and increment by 1 with each new row.

nr
SQLTeam MVY

12543 Posts

Posted - 2008-03-27 : 11:14:02

>> Should IDENTITY (1,1) be in the column def?
yes

>> How do I populate the smallint PK now?
you don't - it's done automatically. Leave it out of the column list for the insert and th next value from the current seed will be used.
Note that this isn't guaranteed to be consecutive or unique.
You need a unique index (PK) to make sure it is unique.
There can be gaps as a failed insert can use up an identity value.

If you want to add a row with a specific value then
set identity_insert mytbl on
insert mytbl (<collist including the identity column>) select ...
set identity_insert mytbl off

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

mikebird
Aged Yak Warrior

529 Posts

Posted - 2008-03-27 : 11:20:32

With uniqueidentifier, the script below is what I was doing for weeks! Now I need to swap the newid() for an alternative for population. These insertion statements happen all the time. What do I put after the SELECT to number the smallint column?

Thanks

insert into mobile_solution_process (msp_id, work_order, received, departed, arrived, completed)
select newid(), left(cid, 10), received, departed, arrived, completed
from [ken-sql-002].hettonhosttestdatabase.dbo.job
where cid not in ('1','2','3','6') -- getting rid of chaff
and left(completed, 10) like left(getdate(), 10)

Imukai
Starting Member

29 Posts

Posted - 2008-03-27 : 11:33:57

As we both indicated above, an identity field is filled in automatically. You don't specify it in your insert statement at all, unless you specifically want to insert a value (as nr demonstrates). If you aren't specifying a specific value, then just take it out of the statement altogether:

insert into mobile_solution_process (work_order, received, departed, arrived, completed)
select left(cid, 10), received, departed, arrived, completed
from [ken-sql-002].hettonhosttestdatabase.dbo.job
where cid not in ('1','2','3','6') -- getting rid of chaff
and left(completed, 10) like left(getdate(), 10)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources