Append Query

[zezo2021]

friends

I have 2 tables with the same fields
I want to append data from table 1 to table 2
my requirement is:
1-if the user press this command s 100 not append-only one
2- if I delete rows from table2 and press append query again will append the deleted records only

Thank you so much

 

[oleronesoftwares]

if the user press this command s 100 not append-only one

Please explain better.

For the second one
You have to insert into another table, before delete same values from the original table
Lets assume two tables
Original table
Temporary table

Insert from original table into temporary table

then delete from original table the inserted values

You have to add the two queries into a macro
Insert runs first then delete runs second.

If you delete without inserting into temporary table, you will not be able to apend deleted records.

Then whenever you want to apend again the deleted records

it is insert from temporary table into original table

 

[plog]

That's not how databases are to work. You don't move data, you don't keep multiple copies of it, you don't shift it around. You build your
tables properly, store your data in them and then reference them when you need the data.


How about you give us a big picture of your database. Tell us what it is for, what real life objects it represents and tracks. Then, give us a higher level view of this particular issue or what you are trying to ultimately accomplish.

 

[oleronesoftwares]

@Plug , I think database is meant to be made to meet the requirements of the user and developers are sometimes required to think outside of the box .

 

[June7]

I agree with Plog. Why is there a need to move records? Most likely just need another field in table to tag records as "inactive" or "completed".

 

[plog]

Olerones---what are the requirements of the user? Which of them necessitates this poor solution? Why is this poor solution the only way to met them?

All the times, not sometimes, good developers should understand the situation before trying to 'help'.

 

[oleronesoftwares]

@plog only zezo2021 can answer that question, but in practice we have implemented projects that entailed thinking on paper in database design to seeing things from the eyes of customer, software is about solving real life solutions and my aim as a developer is to please the customer.

For @zezo2021 to have created such scenerio, its likely because a client wants it like that.

 

[Pat Hartman]

1-if the user press this command s 100 not append-only one
2- if I delete rows from table2 and press append query again will append the deleted records only

1. Add criteria to the append query to select only the record you want to append.
2. Once the records are deleted from table2, they're gone. That is how delete works. If you want to add them back again, you would need to run all the append queries that you ran previously, one at a time, to put the data back.

As some of the others have pointed out, it is rarely necessary to create temporary tables. A deleted flag might serve the purpose but that of course would require changes to your existing queries so that most of them exclude "deleted" rows.

It is important for us to understand the big picture. Sometimes, new developers get fixated on one solution that may or may not be even close to what actually needs to happen. It's like the old adage, if all you have is a hammer, everything looks like a nail. So maybe if you only know how to do one thing, that thing isn't correct in this situation.

 

[Pat Hartman]

For @zezo2021 to have created such scenerio, its likely because a client wants it like that.

If the client wants all numbers displayed as Roman numerals, does that mean that you need to store the numbers as text strings internally? The user doesn't get to decide the internal workings of an application. He is the subject matter expert but he is not the application programmer. It is up to the programmer to understand the requirements and implement them efficiently, not copy them verbatim.