Home » SQL & PL/SQL » SQL & PL/SQL » Dynamic Insert Statement (Oracle 9i)
Dynamic Insert Statement [message #333135] Thu, 10 July 2008 10:18 Go to next message
spmano1983
Messages: 269
Registered: September 2007
Senior Member
Insert Statement

Freinds,

I have table,

man_1 -> emp_no,entry_name1,entry_name2,entry_name3,entry_name4,entry_value1,entry_value2,entry_value3,entry_value_4

I have suppress the values,

user will give like this input

1001
'Training Cost'
'Fees'
'Loan'
'Car Loan'
1000
2000
2000
5000

then,
the table should be



emp_no   entry_name1   entry_name2 entry_name3  entry_name4 
1001     Training Cost Fees        Loan          Car Loan
 entry_value1 entry_value2 entry_value3 entry_value_4
 1000         2000         2000         5000

If input will be,

1001
'Training Cost'
'Fees'
'Loan'
'Car Loan'
NULL
2000
2000
5000

Training cost is null, now the insert statement should be

emp_no  entry_name1 entry_name2 entry_name3 entry_value1 
 1001   Fees        Loan        Car Loan    2000 
 entry_value2 entry_value3 
 2000         5000


The Input Will be

1001
'Training Cost'
'Fees'
'Loan'
'Car Loan'
NULL
NULL
NULL
5000

Then , Insert statement should be

 employee_number  entry_name1 entry_value1 

 1001             Car Loan    5000



Dynamically we should change the insert statement,, like this i have 30 entry_name and entry_value,

this insert statement should be change, depend upon the inputs (i.e,i should avoid the NULL values)

How can i achieve this,,

please share your suggestions..


Thanks
Manoharan

Re: Dynamic Insert Statement [message #333137 is a reply to message #333135] Thu, 10 July 2008 10:20 Go to previous messageGo to next message
BlackSwan
Messages: 25041
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above
Re: Dynamic Insert Statement [message #333140 is a reply to message #333135] Thu, 10 July 2008 10:27 Go to previous messageGo to next message
spmano1983
Messages: 269
Registered: September 2007
Senior Member
Insert Statement

Freinds,

I have table,

man_1 ->
emp_no,entry_name1,entry_name2
,entry_name3,entry_name4
,entry_value1,entry_value2
,entry_value3,entry_value_4

I have suppress the values,

user will give like this input

1001
'Training Cost'
'Fees'
'Loan'
'Car Loan'
1000
2000
2000
5000

then,
the table should be




emp_no entry_name1 entry_name2 entry_name3 entry_name4
1001 Training Cost Fees Loan Car Loan

entry_value1 entry_value2 entry_value3 entry_value_4
1000 2000 2000 5000

If input will be,

1001
'Training Cost'
'Fees'
'Loan'
'Car Loan'
NULL
2000
2000
5000

Training cost is null, now the insert statement should be


emp_no entry_name1 entry_name2 entry_name3 entry_value1
1001 Fees Loan Car Loan 2000

entry_value2 entry_value3
2000 5000


The Input Will be

1001
'Training Cost'
'Fees'
'Loan'
'Car Loan'
NULL
NULL
NULL
5000

Then , Insert statement should be


employee_number entry_name1 entry_value1

1001 Car Loan 5000


Dynamically we should change the
insert statement,,

like this
i have 30 entry_name and entry_value,

this insert statement should be
change, depend upon the
inputs (i.e,i should avoid the NULL values)

How can i achieve this,,

please share your suggestions..


Thanks
Manoharan

Re: Dynamic Insert Statement [message #333219 is a reply to message #333140] Thu, 10 July 2008 21:16 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
anacedent wrote on Thu, 10 July 2008 10:20

Quote:

http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above



Please follow the guidelines.
169 Messages and still you don't know how to format. Shocked

Search for DBMS_SQL on google.

Regards,
Rajat

[Updated on: Thu, 10 July 2008 21:17]

Report message to a moderator

Re: Dynamic Insert Statement [message #333275 is a reply to message #333135] Fri, 11 July 2008 02:05 Go to previous messageGo to next message
spmano1983
Messages: 269
Registered: September 2007
Senior Member
Hi,

Sorry yaar.

Requirement is this only.

Any possibility for do like this,,

input value is

value1 : 1000
value2 : 2000
value3 : 4000

then, my insert statement should be

insert into table1(value1,value2,value3)
values(1000,2000,4000);

if input values are

value1 : NULL
value2 : 2000
value3 : 4000

then

insert statement should be

insert into table1(value1,value2,value3)
values(2000,4000,NULL);

I mean,, i have to order the values by NULL, have to insert the NULL values after the NOT NULL values.. it is possible??? please share your suggestions.
Re: Dynamic Insert Statement [message #333282 is a reply to message #333275] Fri, 11 July 2008 02:32 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
When people ask you to post additional details, it is generally considered rude to ignore them and simply repost your original question again.


Why not put the values to be inserted into an pl/sql table, using the value as both the key to the pl/sql table (when nvl'd) and the value.
That way when you read through the values of the table, they'll be sorted into the required order.
Previous Topic: SQL query
Next Topic: create table from remote db
Goto Forum:
  


Current Time: Tue Dec 06 14:10:50 CST 2016

Total time taken to generate the page: 0.17607 seconds