Dynamically generating "insert" SQL statments

There are times where you need to generate "INSERT" SQL statements from existing data. For example, you may have a production release ready "Delete" SQL statement as shown below to remove some existing records.

Delete from employee_table WHERE first_name like ('P%')

For this script, you need to have a rollback script. It can be dynamically generated as shown below.

SELECT 'INSERT INTO employee_table ( first_name, surname, birth_date, created_date, updated_by) VALUES (' 
|| '''' || first_name || ''' '
|| ',''' || surname || ''' '
|| ',''' || convert(CHAR(20),birth_date) || ''' '
|| ',''' || convert(CHAR(20),created_date) || ''' '
|| ',''' || updated_by || ''' '
FROM employee_table  WHERE first_name like ('P%')

Alternatively, there are database management tools like TOAD, DBArtisan, SQL Developer, etc that allows you to generate SQL statements from the selected records. At times those generated insert statements need to be massaged like removing the auto generated primary key ids, timestamp,  etc. You can make the changes in Notepad++ Find/Replace with regular expression feature. For example, the following generated insert statement

INSERT INTO employee_table (emp_id, first_name, surname, birth_date, created_date, updated_by, timestamp)
   VALUES ( 1365, 'Peter', 'Smith', '03 Dec 1950', '03 Jun 2013 11:15:59.456 AM', 'user1', 0x0000090209F4A6E6)

can be modified with the following regular expressions

For:   emp_id

Find: \(\s* emp_id,

Replace with: (

Find: VALUES\s*\(\s*\d+,

Replace with: VALUES (

For:  timestamp

Find: ,timestamp
Replace with:

Find:0x[0-9A-G]+,

Replace with: