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

user221826 just a moment. 1 answers, 0 views
text-processing awk sed sql

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 Answers


George Vasiliou 03/20/2017.

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 )


HighResolutionMusic.com - Download Hi-Res Songs

1 Martin Garrix

Yottabyte flac

Martin Garrix. 2018. Writer: Martin Garrix.
2 Alan Walker

Diamond Heart flac

Alan Walker. 2018. Writer: Alan Walker;Sophia Somajo;Mood Melodies;James Njie;Thomas Troelsen;Kristoffer Haugan;Edvard Normann;Anders Froen;Gunnar Greve;Yann Bargain;Victor Verpillat;Fredrik Borch Olsen.
3 Sia

I'm Still Here flac

Sia. 2018. Writer: Sia.
4 Blinders

Breach (Walk Alone) flac

Blinders. 2018. Writer: Dewain Whitmore;Ilsey Juber;Blinders;Martin Garrix.
5 Dyro

Latency flac

Dyro. 2018. Writer: Martin Garrix;Dyro.
6 Cardi B

Taki Taki flac

Cardi B. 2018. Writer: Bava;Juan Vasquez;Vicente Saavedra;Jordan Thorpe;DJ Snake;Ozuna;Cardi B;Selena Gomez.
7 Bradley Cooper

Shallow flac

Bradley Cooper. 2018. Writer: Andrew Wyatt;Anthony Rossomando;Mark Ronson;Lady Gaga.
8 Halsey

Without Me flac

Halsey. 2018. Writer: Halsey;Delacey;Louis Bell;Amy Allen;Justin Timberlake;Timbaland;Scott Storch.
9 Lady Gaga

I'll Never Love Again flac

Lady Gaga. 2018. Writer: Benjamin Rice;Lady Gaga.
10 Kelsea Ballerini

This Feeling flac

Kelsea Ballerini. 2018. Writer: Andrew Taggart;Alex Pall;Emily Warren.
11 Mako

Rise flac

Mako. 2018. Writer: Riot Music Team;Mako;Justin Tranter.
12 Dewain Whitmore

Burn Out flac

Dewain Whitmore. 2018. Writer: Dewain Whitmore;Ilsey Juber;Emilio Behr;Martijn Garritsen.
13 Bradley Cooper

Always Remember Us This Way flac

Bradley Cooper. 2018. Writer: Lady Gaga;Dave Cobb.
14 Little Mix

Woman Like Me flac

Little Mix. 2018. Writer: Nicki Minaj;Steve Mac;Ed Sheeran;Jess Glynne.
15 Charli XCX

1999 flac

Charli XCX. 2018. Writer: Charli XCX;Troye Sivan;Leland;Oscar Holter;Noonie Bao.
16 Rita Ora

Let You Love Me flac

Rita Ora. 2018. Writer: Rita Ora.
17 Diplo

Electricity flac

Diplo. 2018. Writer: Diplo;Mark Ronson;Picard Brothers;Wynter Gordon;Romy Madley Croft;Florence Welch.
18 Jonas Blue

Polaroid flac

Jonas Blue. 2018. Writer: Jonas Blue;Liam Payne;Lennon Stella.
19 Lady Gaga

Look What I Found flac

Lady Gaga. 2018. Writer: DJ White Shadow;Nick Monson;Mark Nilan Jr;Lady Gaga.
20 Avril Lavigne

Head Above Water flac

Avril Lavigne. 2018. Writer: Stephan Moccio;Travis Clark;Avril Lavigne.

Language

Popular Tags