Suppose I have a table "foo" with columns a b and c. A is a serial unique key.
What's a good way to copy a record to a temp table, and then append it
back in as a new record, automatically assigning a new serial key?
If I:
create temp table bar as select (*) from foo;
insert into foo (*) select (*) from bar;
Then the new row comes in with the same serial key in column a.
If I explicitly name fields in the CREATE, INSERT and SELECT
clauses (so as to exclude field a), then I have to know and
maintain a list of each and every field and this becomes a
maintenance headache any time the column names change in foo.
I could live with a solution that required me to know just
the name of the one field I want to exclude, such as
create temp table temp as select (* except a) from foo;
insert into foo (* except a) select (* except a) from bar;
I have done a fair amount of old-school DOS database stuff, but
am rather new to SQL, so if this is a simple question, then that's
why I'm asking on the novice list! :)
But surely, there must be a nice general solution to this, since
it would seem to happen in a *large* number of master/transaction
situations, where a ledger of transactions is posted, and then the
posted transactions are archived into a history file, before clearing
the batch to make way for the next batch. Suppose both the batch
file and the history file use unique serial IDs, and each table's
IDs are numbered separately -- one has to be able to append all fields
except the serial from a number of rows, while simultaneously
generating new serials for each of the rows appended....
Once I figure out how, I would create a php subroutine with parameterized
table names and serial field names, and then be able to easily append
serialized records with no other knowledge of the underlying structure
of the specific table being operated upon.
I just can't find a clean way to do it. Suggestions are appreciated.
Thanks, and regards from Portland,
Jim Long
