Changing sql queries in loop using variables and SED command in bash

Question

I have a script in bash I want to replace some variables and also use SED command on top of it.

while IFS= read -r job_name;
do
job_id="$(echo $job_name | cut -c2-5)"
job_set_id="$(echo $job_name | cut -d '_' -f 1)"

sed -i 's/++++TABLE_NAME++++/'${table_name}'/g' | 
echo "
INSERT INTO ++++TABLE_NAME++++
SELECT '$job_id' as job_id, '$job_set_id' as job_set_id from
++++SOURCE_TABLE_NAME+++++;" >>final_output.txt
done < abc.txt

contents of abc.txt

p1234_job_run
p2345_job_run

expected out in final_output.txt file is below

INSERT INTO ${table_name}
SELECT '1234' as job_id, 'p1234' as job_set_id from
${source_table_name}

INSERT INTO ${table_name}
SELECT '2345' as job_id, 'p2345' as job_set_id from
${source_table_name}

output I am getting is below

INSERT INTO ++++TABLE_NAME++++
SELECT '1234' as job_id, 'p1234' as job_set_id from
++++SOURCE_TABLE_NAME+++++;

INSERT INTO ++++TABLE_NAME++++
SELECT '2345' as job_id, 'p2345' as job_set_id from
++++SOURCE_TABLE_NAME+++++;

I have tried changing the script like below

while IFS= read -r job_name;
do
job_id="$(echo $job_name | cut -c2-5)"
job_set_id="$(echo $job_name | cut -d '_' -f 1)"


echo "
INSERT INTO ++++TABLE_NAME++++
SELECT '$job_id' as job_id, '$job_set_id' as job_set_id from
++++SOURCE_TABLE_NAME+++++;" |  sed -i 's/++++TABLE_NAME++++/'${table_name}'/g' >>final_output.txt
done < abc.txt  
    

I am getting below error

sed: no input files

What wrong I am doing and what is the correct method to achieve my result

Answer 1

You can use the shell's word splitting to get the job set ID from the input file, and parameter expansion to get the job ID from that.

Then you can substitute those variables into a here-doc to write to the output file.

Escape the $ in ${table_name} and ${source_table_name} to write them literally into the output file.

while IFS=_ read job_set_id _; do
    job_id=${job_set_id:1}
    cat <<EOF
INSERT INTO \${table_name}
SELECT '$job_id' as job_id, '$job_set_id' as job_set_id from
\${source_table_name};

<<EOF
done < abc.txt > final_output.txt