Insert

INSERT

Many old-timers are used to using the APPEND BLANK command to add a record to a table. The same operation is considerably easier and more efficient with the SQL INSERT command. As opposed to the APPEND BLANK/REPLACE or APPEND BLANK/GATHER command combinations that make two passes at the table-one to add the record, and a second to update the data in the blank record-INSERT does both in one pass. For example:

* create variables that contain data that will be

* placed into the new record*

* this example has a new field for middle initial* store 'David' to cNameFirst store 'L.' to cNameMiddle store 'Bowie' to cNameLast

store to dBirth*

* use SQL INSERT to add and update the record at the same time* insert into IND from memvar

Notice that, as long as IND has fields that match up with memory variables, the INSERT command does not have to be changed when the table structure changes. The INSERT command also has variations that allow you to take into account inserting specific values for specific fields, and inserting values from an existing array instead of from memory variables. You might use the first instance when you want to add a record with data for only certain fields:

insert into IND (iidind, dLastMod) values (1, date())

I always format an INSERT INTO command like so:

insert into IND ;(iidind, dLastMod) ;values ;(1, date())

Unless you have a very small number of fields, you'll have to break the command somewhere, and I have found keeping the field list and values list on separate lines makes it easy to line up each matching field and its corresponding value.

Two common mistakes that send many programmers back to the Language Reference or online help are including the FIELDS keyword in the INSERT INTO command when it's not necessary, like so:

insert into IND ;fields ;(iidin, dlastmod) ;values ;(1, date())

or forgetting the parentheses around the field list or values list. If you do either of these things, VFP will respond with a Syntax Error message.

Don't feel too bad if you do it once or twice-I still do it after years of writing INSERT INTO commands. When inserting values from an array, it's important to note that the array must be structured in the same order as the table. If you don't have data for certain fields, you must still provide empty columns in the array as placeholders for those fields:

decl aNames[1,4]
aNames[1,1] = 'Herman'
aNames[1,2] = ''
aNames[1,3] = 'Munster'
aNames[1,4] =
insert into IND from array aNames

INSERT INTO will add one record for each row in a multi-row array:

decl aNamaes[2,4]

aNames[1,1] = 'Herman'
aNames[1,2] = ''
aNames[1,3] = 'Munster'
aNames[1,4] =
aNames[2,1] = 'Lilly'
aNames[2,2] = 'O.'
aNames[2,3] = 'Munster'
aNames[2,4] =
insert into IND from array aNames