I'm trying to write a function in code to copy a set of rows from a given table to the same table and then alter a couple of the fields (RefNumber field and a TimeStamp field).
I'd like to do a simple INSERT (to copy the existing required rows) and then perform an UPDATE on these same rows to modify the RefNumber and TimeStamp fields accordingly. This sounds easy enough...
However, the parameters passed to my function are the TableName, the existing RefNumber (identifying those rows selected to be copied) and the new target RefNumber (to be changed in an update). This means that I won't know the rows of the table, and so can only do something like:
INSERT INTO TableName SELECT * FROM TableName WHERE RefNumber=<ExistingRefNumber>
...and then do an update of these new rows somehow — but it's not clear how I'd identify these newly created rows either.
The only commonality between the tables that can be passed to my function are that they all have a 'RefNumber' column and all have their own differently named auto-incrementing ID column.
I'm guessing I may need to write and then call a stored procedure which might identify the columns of table 'TableName' and then do the job in one call such as:
INSERT INTO TableName VALUES(Col1,Col2,...,RefNumber, TimeStamp,....ColN) SELECT Col1, Col2,...,<NewRefNumber>, <TimeStampVal>,...ColN FROM TableName WHERE RefNumber=<ExistingRefNumber>
...there may of course be another approach (could I somehow know the auto-ID of the newly created rows in my first suggestion and update these accordingly?) but I'm no SQL expert.
Thanks in advance