Insert data into sql queries from a file

Hello friends,

I need to insert data from a file to another. I need this to form an sql query file which will consist of 50.000 INSERT INTO sentences.

my sql query file will consist of 50.000 times the below line consecutively:

insert into subscriber (BLINDID,NAME,SURNAME,EMAIL,USER_AUTHORIZATIONID,REM_REWARD,STATUS,IS_BLACKLISTED) values ('DATA','name','surname','DATA@test.com','1','1000','3','0');

I need to insert a given list of Subscribers into the places i specifed as DATA above. how can i manage this? The given data consists of 50.000 numbers. Any suggestion, solution wellcomes.

Note: I tried with MS Excel but coudlnt make it because it senses the characters " ', @" in the queries as special formula characters. I tried data generator program also but failed

Thanks in advance
Regards

Assuming you have unix shell.

We will avoid letting shell see the SQL query because it looks like Shell syntax. We'll just use Shell to sequence external commands.

Assuming your list of numbers is in "example.dat" and your skeleton SQL query line is in "example.skl" we can generate the lines by swapping the string "DATA" for a number from "example.dat" and append to "example.sql".

#!/bin/ksh
>example.sql
cat example.dat | while read NUMBER
do
        sed -e "s/DATA/${NUMBER}/g" example.skl >> example.sql
done

sorry but after running the command the example.sql file is getting empty. If i remove the part
>example.sql
and run it it works but replacing NUMBER with each DATA on each line , not replacing with the real numbers.

Please post the shell script you ran and a few lines of sample input data (with anything confidential changed).
What version of unix are you using? What shell?

I am using solaris 10 and bash as it is very friendly shell.

xxxx{root}/>less data_insert.sh 
#!/bin/ksh
cat example.dat | while read NUMBER
do
        sed -e 's/DATA/$NUMBER/g' example.sql >> example_.sql
done

xxxx{root}/>less example_.sql 
insert into subscriber (BLINDID,NAME,SURNAME,EMAIL,USER_AUTHORIZATIONID,REM_REWARD,STATUS,IS_BLACKLISTED) values ('$NUMBER','
name','surname','$NUMBER@test.com','1','1000','3','0');
xxxx{root}/>

if i use the one with
">example.sql"

output file is empty..

my sed does not support -i option too.

I could manage to do it by using function feature of MS Excel at last, thanks for help.

You got single quotes (') around the sed command. You need to use double quotes (") for $NUMBER to get processed by bash.

normally single quotes works but double quotes worked in this case, thnx