By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,379 Members | 1,439 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,379 IT Pros & Developers. It's quick & easy.

Porting Legacy Data to a PK

P: 2
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?
Mar 17 '08 #1
Share this Question
Share on Google+
3 Replies


ck9663
Expert 2.5K+
P: 2,878
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
Mar 17 '08 #2

P: 2
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)
Mar 17 '08 #3

ck9663
Expert 2.5K+
P: 2,878
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
Mar 18 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.