Login or Register to Ask a Question and Join Our Community


Emergency UNIX and Linux Support

Insert data into sql queries from a file

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Homework and Emergencies Emergency UNIX and Linux Support Insert data into sql queries from a file
# 1  
Old 08-24-2010
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:

Code:
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 Smilie

Thanks in advance
Regards
Last edited by EAGL€; 08-24-2010 at 09:55 AM..
# 2  
Old 08-24-2010
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".


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

# 3  
Old 08-24-2010
Quote:
Originally Posted by methyl
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".


Code:
#!/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.
# 4  
Old 08-24-2010
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?
# 5  
Old 08-24-2010
Quote:
Originally Posted by methyl
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.

Code:
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.
Last edited by EAGL€; 08-25-2010 at 04:11 AM..
# 6  
Old 08-25-2010
You got single quotes (') around the sed command. You need to use double quotes (") for $NUMBER to get processed by bash.
This User Gave Thanks to pileofrogs For This Post:
EAGL€
# 7  
Old 08-25-2010
Quote:
Originally Posted by pileofrogs
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
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Storing multiple sql queries output into variable by running sql command only once

Hi All, I want to run multiple sql queries and store the data in variable but i want to use sql command only once. Is there a way without running sql command twice and storing.Please advise. Eg : Select 'Query 1 output' from dual; Select 'Query 2 output' from dual; I want to... (3 Replies)
Discussion started by: Rokkesh
3 Replies

2. Programming

Join 2 SQL queries into one

Dear community, could someone help me to join 2 queries into one? Basically it's the same query with different clauses (please notice the FIELD3 filters and related counters into the subquery): SELECT A.FIELD1,A.FIELD2,A.FIELD3 FROM TABLE A INNER JOIN ( SELECT FIELD1,... (3 Replies)
Discussion started by: Lord Spectre
3 Replies

3. Shell Programming and Scripting

Help with storing the output of multiple sql queries to a file

Hi All, I have a file queries.txt as follows : SELECT COLUMN1 FROM SCHEMA2.TABLE1 MINUS SELECT COLUMN1 FROM SCDEMA2.TABLE2; SELECT COLUMN2 FROM SCHEMA2.TABLE1 MINUS SELECT COLUMN2 FROM SCDEMA2.TABLE2; SELECT COLUMN3 FROM SCHEMA2.TABLE1 MINUS SELECT COLUMN3 FROM SCDEMA2.TABLE2; SELECT... (2 Replies)
Discussion started by: SriRamKrish
2 Replies

4. Shell Programming and Scripting

How can i run sql queries from UNIX shell script and retrieve data into text docs of UNIX?

Please share the doc asap as very urgently required. (1 Reply)
Discussion started by: 24ajay
1 Replies

5. Programming

[SQL] Insert content file to mysql

dear all, i want to insert string in file to mysql i just want how to do that cause i am poor in sql languages ... so this file like this DATA.txt doni|student|westjava|123412|lombok| iwan|student|westjava|1234412|utankayu| rio|student|westjava|12342|cempedak| so i want insert DATA.txt to... (2 Replies)
Discussion started by: zvtral
2 Replies

6. Shell Programming and Scripting

convert file into sql insert stmt

My file is now cleaned, sanitized & prepped: 07/07/2008 21:18:51 Installation 52016 complete *BUT NOTHING CHANGED* 07/21/2008 15:28:15 Removal 52016 complete 07/21/2008 15:34:15 Removal 55856 complete 12/08/2009 19:30:40 Installation 62323 complete 12/08/2009 19:39:06 Installation ... (6 Replies)
Discussion started by: dba_frog
6 Replies

7. Shell Programming and Scripting

How to use sql data file in unix csv file as input to an sql query from shell

Hi , I used the below script to get the sql data into csv file using unix scripting. I m getting the output into an output file but the output file is not displayed in a separe columns . #!/bin/ksh export FILE_PATH=/maav/home/xyz/abc/ rm $FILE_PATH/sample.csv sqlplus -s... (2 Replies)
Discussion started by: Nareshp
2 Replies

8. Shell Programming and Scripting

Run SQL queries in DB2 and output to file

Hi, I new to Unix and scripting. Following is my requirement. Can someone tell me whether its possible or not. Also please let me know how to proceed further if this is possible. List of queries are stored in a file. For example, I have to run a query like this: Select * from &XYZ where... (0 Replies)
Discussion started by: simhasuri
0 Replies

9. UNIX for Dummies Questions & Answers

performing queries on file data

Can anyone help.. I have a .dat file which contains 5 columns of data. I need to select one row containing just 3 of the columns. I'm not sure how to select a single row? I then need perform arithmetic on on two out of the three columns and display the output in a new column. For example.. ... (1 Reply)
Discussion started by: computersaysno
1 Replies

10. UNIX for Dummies Questions & Answers

SQL queries in background?

I have to query a DB2 database, and sometimes they take a long time to produce results. Can I run these queries in the background, and if so, where will the results appear? (1 Reply)
Discussion started by: jpprial
1 Replies
Login or Register to Ask a Question