Porting Legacy Data to a PK

[MatAtNatwell]

I have a DB setup in MSS 2005, into which I have imported a ton of legacy data from an old FoxPro DB. The PKs did not transfer over, so I had to manually set them after the import. Now, I want to insert new data into my SQL DB, but the PK (Let's call it CUST_NUM) won't auto increment. I tried to do a SELECT CONVERT command on the row, but it cant convert numeric to int.

Am I excising a task in futility here, or can I get the increment to pick up on the last entry into the table?

[ck9663]

I have a DB setup in MSS 2005, into which I have imported a ton of legacy data from an old FoxPro DB. The PKs did not transfer over, so I had to manually set them after the import. Now, I want to insert new data into my SQL DB, but the PK (Let's call it CUST_NUM) won't auto increment. I tried to do a SELECT CONVERT command on the row, but it cant convert numeric to int.

Am I excising a task in futility here, or can I get the increment to pick up on the last entry into the table?

How are you "auto-incrementing" the column? Stored Proc? Trigger? Function? IDENTITY?

-- CK

[MatAtNatwell]

Well, and I know that this is probably the wrong way of going about it, here's more detail on the scenario:

the first number in the legacy table is 1001 for Cust_Num. I have the column over to int now, but I want to be able to have it auto-increment from the last entered customer record (320069)

My attempts have been centered around IDENTITY, but if this isn't the way to get it done, I can go other ways.

Below is my query thus far:

ALTER TABLE dbo.CUST ALTER COLUMN [CUST_NUM] IDENTITY (320070, 1)

[ck9663]

The first number (I think) on that is the starting point.

Create another field as int on the table. Make that an IDENTITY column. It will automatically be populated. Compare your ACCT_NUM and that new field. If they exactly match. Drop your ACCT_NUM and rename the new field as ACCT_NUM.

Remember that IDENITY ensures UNIQUENESS and not SEQUENCE. If you delete a row on your table, that number will not be reused the next time you insert a new row.

-- CK