Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

How to use loop to convert a DML statement into DDL?

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting How to use loop to convert a DML statement into DDL?
# 1  
Old 09-18-2010
How to use loop to convert a DML statement into DDL?
Hi Unix Gurus,

I am a newb. I am creating a script which will use an input file. This input file can have 1 or more than 1 DML staments like INSERT/DELETE/UPDATE.

I have to execute these statements using my script but before execution of these DML statements, I need to check the count for each statement.

For Example: Suppose the input file has following statements:

Code:
update Employee set emp_id=10 where emp_id=1;
update Employee set emp_id=20 where emp_id=2;
update Employee set emp_id=30 where emp_id=3;
update Employee set emp_id=40 where emp_id=4;
update Employee set emp_id=50 where emp_id=5;


Now, before updating the table, I need to check the count of each statement.


Please suggest how can I do this. I am absolutely new to UNIX and do not have any idea how to do this.

Thanks in advance...!!!!

Regards
A Novice... Smilie
Last edited by Scott; 09-19-2010 at 06:26 AM.. Reason: Code tags
# 2  
Old 09-18-2010
Hi

You can search in this forum itself. You will find many links related to this. To get a basic idea of how to begin, you can check this: connect to sql and retrieve/update data:

Guru.
# 3  
Old 09-18-2010
Hi Guru,

The link you posted is very helpful but here I am looking for some way to find how to check count if I have DML statements in an input file. The number of statement is not fixed. There may be 1 statement or more than 1 statement. Plus there could be Insert / Delete /Update statement. I am not sure what kind of DML would be there.

1 thing is fixed, if the input file has Update statement then it would not have rest 2 (Insert/Delete). But number of statements is not fixed.

What I have in mind is we can check the exact count of statements present in the input file. Then we can use a loop which will run for each statement and create a SELECT COUNT(*) statement with its WHERE clause.

But here 1 have another problem, how would I use grep to extract the entire where clause.

Any suggestions on this....
# 4  
Old 09-18-2010
Code:
$ 
$ 
$ cat f10
update Employee set emp_id=10 where emp_id=1;
update Employee set emp_id=20 where emp_id=2;
update Employee set emp_id=30 where emp_id=3;
update Employee set emp_id=40 where emp_id=4;
update Employee set emp_id=50 where emp_id=5;
__
insert into Employee (emp_id) values (100);
insert into Employee (emp_id) values (200);
insert into Employee (emp_id) values (300);
--
delete from employee where emp_id = 200;
delete from employee where emp_id = 300;
$ 
$ 
$ 
$ perl -ne 'if (/^update/){$u++} elsif(/^insert/){$i++} elsif(/^delete/){$d++}
            END {printf("Updates = %d\nInserts = %d\nDeletes = %d\nTotal = %d\n",$u,$i,$d,$u+$i+$d)}' f10
Updates = 5
Inserts = 3
Deletes = 2
Total = 10
$ 
$

tyler_durden
# 5  
Old 09-18-2010
Hi

If your requirement is to simply run the update queries and retrieve the count of rows of each update query, update your input file like this:

Code:
select count(*) from Employee where emp_id=1;
update Employee set emp_id=10 where emp_id=1;
select count(*) from Employee where emp_id=2;
update Employee set emp_id=20 where emp_id=2;

Now, run this file as in example 4 in the link above. You need not loop on them, they can be done in one shot.

To know, whether the input file has any update statement or not, do something like this:

Code:
grep -c ^update input_file

if the above count is greater than 0, you have updates in it.

Guru.
Last edited by Scott; 09-19-2010 at 06:26 AM..
# 6  
Old 09-18-2010
Hi,

@ Tyler_Durden: thanks for the suggestion. The command you wrote will definitely give me the count. But that is not the issue here.

First, there would be only one type of statement. Either Insert or update or delete, not all 3.

Second, I need to check the count of the impacted rows for each DML statement.

Thanks for helping me, do you have any command for this???

-----------------------------------------------------------------------------

Hi Guru,

Thanx a lot. Here I want to first check the count of all the statements present in the input file. If the count of all the statements (impacted rows) is less than 100,000 records then only I want to execute the DML statements. If the count for even a single statement is going to affect more than 100,000 records then I want that this should be caught in SELECT COUNT(*) statement which will stop processing. That is why I first want to check the count and then it will execute the DMLs.

Thanks
# 7  
Old 09-19-2010
not sure what DBMS your using the you can get this information via explain. running select count(*) for each of those seems very ineffectient. hopefully the emp_id column is indexed.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Convert Update statement into Insert statement in UNIX using awk, sed....

Hi folks, I have a scenario to convert the update statements into insert statements using shell script (awk, sed...) or in database using regex. I have a bunch of update statements with all columns in a file which I need to convert into insert statements. UPDATE TABLE_A SET COL1=1 WHERE... (0 Replies)
Discussion started by: dev123
0 Replies

2. Shell Programming and Scripting

Can we convert 3 awk statements in a single statement

Hi, Can we use 3 statements convert in a single statement. First statement output using the second statement and the second statement output using the third statement please let me know the syntax so that I can able to merge all the three statement. (2 Replies)
Discussion started by: Priti2277
2 Replies

3. Shell Programming and Scripting

While loop within if statement

Hi, I'm a rookie who is trying to learn this stuff. What I need help with is putting together a non complicated "while" loop within the below "if" statement. I also need the while loop to keep looping until the user types a key to end the loop. Please reveal the proper insertion points. Thank... (4 Replies)
Discussion started by: jefferj54
4 Replies

4. Shell Programming and Scripting

Generate a DML dynamically based off of header record

I have the following scenario where I need to use a header record from a file and generate a DML based off of it... E.g.: The header can change periodically with an additional column in between or remove a col.... Sample header : (head -1 sample.txt)... (17 Replies)
Discussion started by: anduzzi
17 Replies

5. Shell Programming and Scripting

Create SQL DML insert statements from file using AWK or similar

Hi all. This is my first post on this forum. I've previously found great help in the huge knowledgebase that is here, but this time I have not been able to find a solution to my problem. I have a large text file that looks like this: typedef struct ABC_struct_nbr1_ { char attr1; /*... (0 Replies)
Discussion started by: Yagi Uda
0 Replies

6. Shell Programming and Scripting

how to create a loop in an if statement

Hey guys, a=`cat abc | wc -l` b=`cat def | wc -l` if $a== $b then echo "a" else echo "b" fi I want the if condition to retry itself , untill a==b. I can't use goto statemt. Please help. Thanx in advance. Please use next time code tags for your code and data (5 Replies)
Discussion started by: jaituteja
5 Replies

7. Shell Programming and Scripting

Convert perl-statement to /bin/sh shell

Hi, I'm doing a small shellscript which is going to take each line in a "queue file" and do stuff to them. I can do the script easily, but I'd like this one to be a bit prettier. Consider the following perl statement: ... foreach my $line (@filedata) { my ($a, $b, $c) = split(/\t/,... (4 Replies)
Discussion started by: brightstorm
4 Replies

8. Shell Programming and Scripting

Pass a DDL statement to a KSH script

I need to pass a DDL statement into a ksh script & parse the statement. What is the best way to pass a DDL statement into a KSH script. ---------- Post updated at 09:28 AM ---------- Previous update was at 07:35 AM ---------- if the name of the script is test.ksh test.ksh "ALTER TABLE... (12 Replies)
Discussion started by: gayathree
12 Replies

9. Shell Programming and Scripting

How to convert unix command into Awk statement

Hi all, How can i use the below unix command in AWK . Can any one please suggest me how i can use. sed -e "s/which first.sh/which \$0/g" $shell > $shell.sal where $0=current program name(say current.sh) $shell=second.sh (1 Reply)
Discussion started by: krishna_gnv
1 Replies

10. UNIX for Dummies Questions & Answers

if statement in a while loop

#!/usr/bin/ksh echo Please enter while read n do echo $n >> datafile done question: How can I enject an if statement that if the users enter 0 (zero) the program will exit? this is what I have but not working #!/usr/bin/ksh echo Please enter number while read n do if $n=0 then... (2 Replies)
Discussion started by: bobo
2 Replies
Login or Register to Ask a Question