Skip to main content

Use a list of strings to replace a sql parameter printing the whole file for each replacement [Resolved]

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

Asked March 20, 2017
Posted Under: Unix Linux
62 views
1 Answers

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 )


Answered March 20, 2017
 
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 5 mins ago
 CanDoerz  6 months ago
 
@user221826 one more bash alternative – George Vasiliou 28 mins ago
 CanDoerz  6 months ago
 
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 50 mins ago
 CanDoerz  6 months ago
 
pardon my naivete but what are variables NR and FNR ? – user221826 56 mins ago
 CanDoerz  6 months ago
Your Answer
D:\Adnan\Candoerz\CandoProject\vQA