What is the best way to move a record from one table to another? In other words, I need to do a query, do some checking on the record, and then insert into another table. Do I have to get each field by its respective type and build a new statement, or is there a simpler approach?
What you can do for this may depend on if both tables are in the same DB, what DB you are using and where you are willing to put the code (Java side or DB side). If both tables are in the same DB, I personally would write a Stored Procedure in the database to do this. The DB would take parameters in, check if you want to move the field and then move the field. Depending on your preference, you can either return a flag to tell the Java program the logic worked or throw an exception back to the calling client (java) if it did not work (and this is an exception condition in you app). I use Oracle where I am working right now. I usually wrap all of my DB inserts in stored procs. If a condition of the insert is not met, I simple throw an exception back to the calling program. Putting the logic in the stored proc allows the same logic to be used from anywhere (Java, VB, Unix) that may call the proc.
Ken, I don't have an option on the server side. Besides I need to pull it to the client-side for visual checks and such before inserting it into another table (which is in the same DB). So, I'm trying to find the stream-lined way of taking the record from the ResultSet and inserting it into a look-a-like table.