Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
APPEND FROM sorry about the post all I needed to do was try it and see
- Thread starter carolx
- Start date
Mike Gagnon
Programmer
If SET DELETED is off they won't append
Mike Gagnon
If you want to get the best response to a question, please check out FAQ184-2483 first.
Mike Gagnon
If you want to get the best response to a question, please check out FAQ184-2483 first.
Olaf Doschke
Programmer
Carolx, good you found out yourself. Indeed it's just a test and you know, nothing you need to ask, as you can answer that very fast using two dbf copies deleting some records in source.dbf, append to destination.dbf and see what is appended. A matter of minutes. You will seldom get a faster answer via forums. Forum questions have an answer within the same day, perhaps, but you can see yourself.
Mike: If set deleted is OFF, deleted records are appended. While append is a bulk operation, it triggers insert triggers in the destination table per appended record. That alone suggests append is not really a bulk copy, it copies record by record. And the DELETED setting applies as always and anywhere. Why should there be an exception?
Simple code
The last three rows of curDest are an exact copy of curSouce, including the deleted record, the first two are the data of the two not deleted records.
Mike, Perhaps you come to your conclusion, because you browsed the result cursor or alias with SET DELETED ON in the end. You have to SET DELETED OFF to browse and see, if deleted records were appended, of course. With SET DELETED ON you won't see them. It's was something I also had to look up regularly, but now I know by heart: SET DELETED ON means you won't see deleted records, the deletion flag functionality is set active (ON), not the visibility of deleted records. It works and suppresses records in any operation from browse, select queries and also append. update queries also don't update deleted records, etc.
Carolx, and all interested in further explanations:
It's as simple and as expected as it can be, SET DELETED ON works in conjunction with EVERYTHING, it has no exceptions. ON activates the functionality of the deletion flag and turns OFF the visibility of deleted records. That's what often causes confusion, but it works as intended. With ON the flag is in action, and everything, also APPEND, does NOT copy deleted records, no commands or functions or any other VFP language or classes see deleted records then.
If you work with SET DELETED ON all the time, the effect of copying the deleted record won't matter in the end, you're just wasting space in the destination cursor or table, if you also append deleted records. They append including the deletion flag and therefore remain deleted in the destination table, too.
That's a different thing, if you instead copy over records by INSERT INTO curDest SELECT * FROM curSource. See for yourself with SET DELETED OFF. But that's another nature of select queries: If SET DELETED is OFF, Select-SQL sees deleted data, as if it never was deleted, and in it's result the deletion flag is NOT copied and therefore always unset(!), You will never get any sql query copying the deletion flag, that's because it does not work on the record low level, it is set related and a field list instead of * is the normal case, it normally does read fields of several source tables and creates result sets with their own schema, querying data from a table with all fields is just a basic case of using SQL.
The nature of APPEND on the other hand is, it copies records on the low level of the DBF file structure, including the deletion flag, but only copies deleted records, if SET DELETED OFF permits reading them and does not suppress them. If it can read them, it reads and copies it including the flag.
It therefore matters if you APPEND or use the SQL approach.
The warning about this is: Only use the deletion flag to delete records, not as a boolean flag, an extra field. It's not meant as your safety net and not at all to store a binary extra info in them. If you only let VFP use it for it's intention to get rid of records fast, without the need to physically remove them, then it means what it's intended for. Also APPEND means what it's meant for and SQL-Select, too. And though they differ, everything is as intended, described and therefore, if you expect something else, your expectations are wrong. Simply always work with SET DELETED ON, and you're done and won't see that different behaviour anyway.
Setting it OFF is for the "expert" really knowing what he's doing, if he sets it off. You then can see at something not existing anymore in the normal database. Deleted are gone. SET DELETED ON and you have exactly that and there never is a problem with that flag. Be warned you have to do this for every data session, but the good news is it's the default. In the IDE the default is ON, too, you set at save it as default in the Tools->Options data tab "Ignore deleted records".
Bye, Olaf.
Mike: If set deleted is OFF, deleted records are appended. While append is a bulk operation, it triggers insert triggers in the destination table per appended record. That alone suggests append is not really a bulk copy, it copies record by record. And the DELETED setting applies as always and anywhere. Why should there be an exception?
Simple code
Code:
* Creating Source data
Create Cursor curSource (cData C(10) default Sys(2015))
Append Blank
Append Blank
Delete
Append Blank
* Creating an empty Destination
Create Cursor curDest (cData C(10) default Sys(2015))
Select curDest && should be selected anyway, just to make sure and clear it's the destination
Set Deleted On
Append From Dbf("curSource")
Set Deleted Off
Append From Dbf("curSource")
BrowseMike, Perhaps you come to your conclusion, because you browsed the result cursor or alias with SET DELETED ON in the end. You have to SET DELETED OFF to browse and see, if deleted records were appended, of course. With SET DELETED ON you won't see them. It's was something I also had to look up regularly, but now I know by heart: SET DELETED ON means you won't see deleted records, the deletion flag functionality is set active (ON), not the visibility of deleted records. It works and suppresses records in any operation from browse, select queries and also append. update queries also don't update deleted records, etc.
Carolx, and all interested in further explanations:
It's as simple and as expected as it can be, SET DELETED ON works in conjunction with EVERYTHING, it has no exceptions. ON activates the functionality of the deletion flag and turns OFF the visibility of deleted records. That's what often causes confusion, but it works as intended. With ON the flag is in action, and everything, also APPEND, does NOT copy deleted records, no commands or functions or any other VFP language or classes see deleted records then.
If you work with SET DELETED ON all the time, the effect of copying the deleted record won't matter in the end, you're just wasting space in the destination cursor or table, if you also append deleted records. They append including the deletion flag and therefore remain deleted in the destination table, too.
That's a different thing, if you instead copy over records by INSERT INTO curDest SELECT * FROM curSource. See for yourself with SET DELETED OFF. But that's another nature of select queries: If SET DELETED is OFF, Select-SQL sees deleted data, as if it never was deleted, and in it's result the deletion flag is NOT copied and therefore always unset(!), You will never get any sql query copying the deletion flag, that's because it does not work on the record low level, it is set related and a field list instead of * is the normal case, it normally does read fields of several source tables and creates result sets with their own schema, querying data from a table with all fields is just a basic case of using SQL.
The nature of APPEND on the other hand is, it copies records on the low level of the DBF file structure, including the deletion flag, but only copies deleted records, if SET DELETED OFF permits reading them and does not suppress them. If it can read them, it reads and copies it including the flag.
It therefore matters if you APPEND or use the SQL approach.
The warning about this is: Only use the deletion flag to delete records, not as a boolean flag, an extra field. It's not meant as your safety net and not at all to store a binary extra info in them. If you only let VFP use it for it's intention to get rid of records fast, without the need to physically remove them, then it means what it's intended for. Also APPEND means what it's meant for and SQL-Select, too. And though they differ, everything is as intended, described and therefore, if you expect something else, your expectations are wrong. Simply always work with SET DELETED ON, and you're done and won't see that different behaviour anyway.
Setting it OFF is for the "expert" really knowing what he's doing, if he sets it off. You then can see at something not existing anymore in the normal database. Deleted are gone. SET DELETED ON and you have exactly that and there never is a problem with that flag. Be warned you have to do this for every data session, but the good news is it's the default. In the IDE the default is ON, too, you set at save it as default in the Tools->Options data tab "Ignore deleted records".
Bye, Olaf.
- Status
Similar threads
- Locked
- Solved
- Locked
- Question
- Locked
- Question
