Converting an Int field to an indentity

Is it possible to convert an int field into an identity field without losing any of the previous values in the int field?

Simon.

Identities are generally in numeric order...so switching from an int field to an Identity field may renumber them but I'm not sure.

actually, I just tried it and I didn't lose any of the values that were there when I changed it from and int to an identity.

What sql string did you use? Alter table alter column [column name] int indentity([LastNumber in the database plus 1,1)?

I needed to migrate three tables from one database to another.
In my case the BOX table with an identity field is used to index into a second table SERIALS.
In a perfect world no records would be deleted but in my case records do get deleted, so simply copying records and inserting
into a second database creates different identities when there are deletions in the source table.

The solution is as follows using a cursor for the box_id

1. Select max(identity_field) from source table into a variable.

2. Create and populate the target table in the second database with that many records. Fill not null fields with bland data and in one field write an unusual value which cant exist in the source table e.g. Production_Line = 99

3. Iterate through the source table and update the target table with
Update Target set fld1,fld2... where Target.Identity_fld = Source.Identity_fld

4.Finally delete in the Target table all records with Production_Line = 99

In the example below, The lotnumber migration is easy since no identity fields are used, but the serial numbers table is based on the identity field in the box table ( A box contains ten serial numbered items) so simply allowing the identity field in the target database to naturally increment will destroy the box --> serials relationship.

Migrating 300,000 serials in 30,000 boxes takes about 5 minutes


/************************************************** ********************************
** File Name: c_MigrateData_to_Live (from the test database)
**
** Description: Copies Three tables LotNumber --> Box -- DB_Serials to a target database dbo.Live
**
** This is used when you have just created a clean empty database and you have
** another database with Lot-Box-Serials to be migrated
** It first gets the max of Identity in the source database table BOX with Max(Identity Field)
** Then creates this many "empty boxes" with production-line = 99
** Now insert the lotnumber table and then update the target box table data using the identity field
** Then insert serials using the same identity field values.
** Finally delete boxes with prod_line=99
**
** Creation Date: 3/11/2004
**
** Author(s): RangeCowboy
**
** System(s): All
************************************************** *******************************/
CREATE procedure c_MigrateData_to_Live
as
declare @BoxCount int, @Table_Identity_Field int
select @Table_Identity_Field=1
select @BoxCount = max(box_id) from DB_test.dbo.box
while (@Table_Identity_Field < @BoxCount)
begin
insert into DB_live.dbo.box
(
box_val,
prod_line, -- These are three not null fields in my table BOX
name
)
values(@Table_Identity_Field,99,1)
select @Table_Identity_Field = @Table_Identity_Field+1
end



insert into DB_live.dbo.lotnumber --Simple insert from source, No identities
(
Lot_no,
name,
boxcount,
date_val
)
select
Lot_no,
name,
boxcount,
date_val
from DB_test.dbo.Lotnumber


declare
@box_val varchar(18),
@prod_line int,
@pallet_id int,
@partial char(1),
@cnt tinyint,
@name int,
@Hold bit,
@Lot_no varchar(7),
@boxcnt int,
@closed bit



declare tempcur cursor for
select box_id from DB_test.dbo.box
open tempcur
fetch next from tempcur into @Table_Identity_Field
while (@@fetch_status = 0)
begin
select
@box_val=box_val,
@prod_line=prod_line,
@Pallet_id=pallet_id,
@partial=partial,
@cnt=cnt,
@name=name,
@Hold=Hold,
@lot_no=Lot_no,
@boxcnt=boxcnt,
@closed=closed
from DB_test.dbo.box
where box_id = @Table_Identity_Field

update DB_live.dbo.box
set
box_val=@box_val,
prod_line=@prod_line,
Pallet_id=@pallet_id,
partial=@partial,
cnt=@cnt,
name=@name,
Hold=@Hold,
lot_no=@Lot_no,
boxcnt=@boxcnt,
closed=@closed
where box_id = @Table_Identity_Field

fetch next from tempcur into @Table_Identity_Field
End

CLOSE tempcur
DEALLOCATE tempcur

insert into DB_live.dbo.DB_serials
(
serialnumber,
box_id
)
select
serialnumber,
box_id
from DB_test.dbo.DB_serials


delete from DB_live.dbo.box where prod_line=99
GO

Check the SQL Server Books Online for SET IDENTITY_INSERT ON.

This allows you to transfer a table's current identity values into a new identity column without renumbering.

http://msdn.microsoft.com/library/e...et-set_7zas.asp

Excellent !

Thank you Doug, I did not know that (obviously)