Creating an incremental field in a create table statement

[WCL01]

I'm looking to create a table that fills in with data from other tables however I have to fill the first field with an incrementing value. I have posted my current statement below that does not work. It seems to put in different numbers in each field instead of 1-30.

INSERT into 'era_proj_actuals' (id1, prog_id, serv_cat, oneyrcontract_id, oneyrstartdt, oneyrenddt, conno, serv_enc, serv_clients) SELECT RECNO() as id1, projections.prog_id, projections.serv_cat, projections.oneyrcontract_target_id, contract_targets.startdate, contract_targets.enddate, ai_contract.conno, projections.serv_enc, projections.serv_clients FROM projections INNER JOIN contract_targets ON projections.oneyrcontract_target_id = contract_targets.oneyrcontract_target_id INNER JOIN ai_contract ON contract_targets.contract_id = ai_contract.ai_contract_id

 

[JRB-Bldr]

The first question is what version of VFP are you using.

Some support Auto-Incrementing fields and other versions do not.

Good Luck,
JRB-Bldr

 

[Mike Lewis]

The way I usually do this is to leave blank the column that is to contain the incrementing value. Then, after you have created the table, execute this command:

UPDATE era_proj_actuals SET ID1 = RECNO()

Once you have initially populated the column in question in this way, you can change its data type to Integer (Autoinc), and then it will automatically be incremented in the future. Use ALTER TABLE to change the data type. Integer (Autoinc) is the equivalent of SQL Server's IDENTITY attribute.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Mike Lewis]

Another point:

If you do use ALTER TABLE to set the ID column to Integer (Autoinc), you will also need to set the NEXTVALUE to one higher than the existing highest value.

Check the VFP Help for ALTER TABLE for the exact syntax.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[clipper01]

I've had nothing with trouble with auto-incs , never use 'em . I use the sys(2015) function for this kind of thing , this is the same as you will see right through scx,vcx files. It is 'unique' to maybe 30 billion records , not quite a GUID , but never fails and very easy to debug/audit trail especially if u ever mix-up which auto-inc integer is coming from where.

 

[Mike Lewis]

I've had nothing with trouble with auto-incs

I've been using AutoIncs since they were first introduced in VFP, and have never had the slightest problem with them. I think most people are happy with them.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Olaf Doschke]

Mike,

I agree. The autoinc functionality without question is stable, even better than with a stored proc and default value calling that stored proc.

I also agree to clipper on the other side. One quirk is how autoincerror works, if you need to append data. You need to get a good understanding for using it correctly.

Another quirk is, that updatable views on autoinc tables don't autoinc, if you append or insert data into them, their id value is 0 for all new records. Views don't inherit any default values anyway, so that's no real surprise or news.

The issues are the same with remote database tables and remote views or CA cursors anyway and always have been, so the autoinc implementation isn't worse than any other similar implementation in MySQL, MS SQL Server, Access or elsewhere.

You can overcome this, and the simples way is to go for random keys you can generate client side without server involvement, having the advantage to also work offline, disconnected or in distributed data bases.

Bye, Olaf.

 

[Olaf Doschke]

@WCL01,

If you still want to go with autoinc despite of our side discussion, what you need to do then is to omit id1 from the insert, after having defined it as autoinc integer. And you define a field as autoinc integer via

Create Table era_proj_actuals (id1 Integer Autoinc NextValue 1 Step 1,....)

As said Alter Table also offers to alter a normal integer to an autoinc the same way as any other column type alteration is done - similar to Create Table:

Alter Table era_proj_actuals Alter Column id1 Integer Autoinc NextValue ... Step 1

First of course determine max(id1) of all current data as the nextvalue (denoted as ...). And afterwards you do:

INSERT into era_proj_actuals (prog_id, serv_cat, oneyrcontract_id, oneyrstartdt, oneyrenddt, conno, serv_enc, serv_clients) SELECT projections.prog_id, projections.serv_cat, projections.oneyrcontract_target_id, contract_targets.startdate, contract_targets.enddate, ai_contract.conno, projections.serv_enc, projections.serv_clients FROM projections INNER JOIN contract_targets ON projections.oneyrcontract_target_id = contract_targets.oneyrcontract_target_id INNER JOIN ai_contract ON contract_targets.contract_id = ai_contract.ai_contract_id

Bye, Olaf.

 

[clipper01]

WCL01 , fyi , here is the way I use the random key sys(2015)
1) in the table that want to 'autoinc' , instead of intId , use something like called Rkey , char 10 , this u will fill with sys(2015)
2) usually will add index tag for this
3) in any insert code you then do someting like
INSERT into era_proj_actuals (rkey,prog_id, serv_cat, .... )
values(sys(2015),projections.prog_id, projections.serv_cat...)
4)then just cross-reference that the same as you would an autoInc in whatever code you are doing
5) u can also back-fill old tables
e.g. replace all rkey with sys(2015) will generate a seperate unique key

the JOINS etc are then exactly the same as you do with an autoInc field.