How to use sed to insert a line before each line in a file with the original line's content surrounding by a string?

Question

I am trying to use sed (GNU sed version 4.2.1) to insert a line before each line in a file with that line's content surrounding by a string.

Input:

truncate table ALPHA;
truncate table BETA;
delete from TABLE_CHARLIE where ID=1;

Expected Result:

SELECT 'truncate table ALPHA;' from dual;
truncate table ALPHA;
SELECT 'truncate table BETA;' from dual;
truncate table BETA;
SELECT 'delete from TABLE_CHARLIE where ID=1;' from dual;
delete from TABLE_CHARLIE where ID=1;

I have tried to make use of the ampersand (&) special character, but this does not seem to work. If I put anything after the ampersand on the replacement string, the output is not correct.

Attempt 1:

sed -e "s/\(.*\)/SELECT '&\n&/g" input.txt

output:
SELECT 'truncate table ALPHA;
truncate table ALPHA;
SELECT 'truncate table BETA;
truncate table BETA;
SELECT 'delete from TABLE_CHARLIE where ID=1;
delete from TABLE_CHARLIE where ID=1;

With the preceding code, I get the SELECT ' as expected, but once I attempt to add ' from dual; to the right side of string, things get out of whack.

Attempt 2:

sed -e "s/\(.*\)/SELECT '&' from dual;\n&/g"  input.txt

output:
' from dual;cate table ALPHA;
truncate table ALPHA;
' from dual;cate table BETA;
truncate table BETA;
SELECT 'delete from TABLE_CHARLIE where ID=1;' from dual;

Answer 1

You can take advantage of the hold space to temporarily store the original line.

sed "h;s/.*/'SELECT '&' from dual;/;p;g" input.txt

or more readably:

sed "
h
s/.*/'SELECT '&' from dual;/
p
g" input.txt

Here's a breakdown of the command.

1. First, each line of the input is placed in the pattern space.
2. The h command copies the contents of the pattern space to the hold space.
3. The s command performs a substitution on the pattern space. The & represents whatever was matched. This command leaves the hold space unaffected.
4. The p command outputs the contents of the pattern space to standard output.
5. The g command copies the contents of the hold space to the pattern space.
6. By default, the contents of the pattern space are written to standard output before reading the next input line.

---

As Glenn Jackman points out, you can replace p;g with G. This builds up a two-line value in the pattern space that is then printed, rather than print two separate pattern spaces.

sed "h;s/.*/'SELECT '&' from dual;/;G" input.txt

Also, you can add comments to the sed command so that you can understand what the line noise does later :), if this is in a script.

sed " 
# The input line is first copied to the pattern space

h                               # Copy the pattern space to the hold space
s/.*/'SELECT '&' from dual;/    # Modify the pattern space
p                               # Print the (modified) pattern space
g                               # Copy the hold space to the pattern space

# The output of the pattern space (the original input line) is now printed
" input.txt