1

I have a txt file with string values in a single column :

File.txt

A

B
.
.
.
Z

I have a sql script with a variable called 'parameter'.

script.sql

declare @var varchar(255)
set @var = 'parameter'
select * from table_name where column = @var
go

I want to read the file line by line and use each line to replace parameter in the sql , print out all sql statements to a file (and eventually run that sql)

I am open to use perl or python, but a simple shell solution would be much appreciated.

E.g. something like: cat file.txt |sed "s/parameter/$0/" script.sql

Expected output for e.g. A and B :

NewFile

declare @var varchar(255)
set @var = 'A'
select * from table_name where column = @var
go
declare @var varchar(255)
set @var = 'B'
select * from table_name where column = @var
go
1

The bash way with the pitfall to call sed everytime (performance penalty applies):

while read -r line;do
  sed -r "s/parameter/$line" script.sql >>newfile.sql
done <file.txt

another bash way:

old="$(<script.sql)"
while read -r ln;do
echo "${old/parameter/$ln}" >>newfile.sql
done <file.txt

The awk way:

awk 'NR==FNR{a[FNR]=$0;last=FNR;next}{for (i=1;i<=last;i++) \
print (a[i] ~ /^set @var/)?gensub(/parameter/,$0,"g",a[i]):a[i] >>"new.sql"}' script.sql file.txt

awk test online here (press execute button above code )

  • pardon my naivete but what are variables NR and FNR ? – user221826 Mar 20 '17 at 21:02
  • Are awk built-in variables used for Number Record (NR) along all files, and File Number Records (FNR) for file under process (resets on every file read next). The check NR==FNR means do the {action} for the first file only – George Vasiliou Mar 20 '17 at 21:09
  • @user221826 one more bash alternative – George Vasiliou Mar 20 '17 at 21:31
  • It works perfectly. However I am still unable to understand the first line , 'NR==FNR{a[FNR]=$0;last=FNR;next}{for (i=1;i<=last;i++) \ – user221826 Mar 20 '17 at 21:54
  • 1
    @user221826 - the two-liner is for readability - don't mix them: the for loop is executed only when reading the second input file; the stuff that precedes it is executed only when reading the first input file – don_crissti Mar 20 '17 at 22:10
0
read -d '' -r SQL < script.sql
while IFS= read -r par; do printf '%s\n' "${SQL/\'parameter\'/\'$par\'}"; done < File.txt

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.