10-10-2007 10:18 AM
i have an internal table having data as follows.
emp_id name date proj_id activity_id Hours Remarks
101 Pavan 09.10.2007 123 1 2.00 Coding
101 Pavan 09.10.2007 124 2 1.00 Documentation
102 Raj 09.10.2007 123 3 6.00 Testing
Now i need to update a Ztable with above mentioned data.
The structure of the Ztable is as follows.
Mandt emp_id name date proj_id activity_id Hours Remarks
NOte: i have ticked both check boxes for the field MANDT in table.
Rest didnt select the check boxes.
I believe now the field MANDT alone is a primary key for the z-table.
NOw i have tried with UPDATE/INSERT statments to update the database table.
But instead of inserting all the rows, the system is over writing on the same emp_id row.
Even tried with the statments like INSERT INTO <Ztable> values <Internal table> ACCEPTING DUPLICATE KEYS.
But its not getting inserted as a separate row in the table.
Requirement is to insert the multiple rows in the database table without any over writing activity.
Can anyone give me the code to do this?
Regards
Pavan
10-10-2007 10:32 AM
Hi Pavan,
Please let me know what are the key fields in your Ztable. Try with below code it may help you. change the "Ztablename" as your database table name and I_INTERNAL TABLE as your internal table name. still you are facing the problem please let me know.
lock the custom table before updating the table.
CALL FUNCTION 'ENQUEUE_E_TABLE'
EXPORTING
MODE_RSTABLE = 'E'
TABNAME = 'ZTABLENAME'
VARKEY =
X_TABNAME = ' '
X_VARKEY = ' '
_SCOPE = '2'
_WAIT = ' '
_COLLECT = ' '
EXCEPTIONS
FOREIGN_LOCK = 1
SYSTEM_FAILURE = 2
OTHERS = 3
.
IF SY-SUBRC <> 0.
MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
ELSE.
INSERT ZTABLENAME FROM TABLE I_INTERNALTABLE ACCEPTING DUPLICATE KEYS.
COMMIT WORK.
ENDIF.
unlock after updating the custom table After updation is done.
CALL FUNCTION 'DEQUEUE_E_TABLE'
EXPORTING
MODE_RSTABLE = 'E'
TABNAME = 'ZTABLENAME'
VARKEY =
X_TABNAME = ' '
X_VARKEY = ' '
_SCOPE = '3'
_SYNCHRON = ' '
_COLLECT = ' '
.
10-10-2007 10:20 AM
Hi ,
Remove the primary key status of MANDT field.
Regards,
Nivetha.
10-10-2007 10:22 AM
Hi,
I don't understand why the data isn't inserted the way you want. Maybe there's something wrong in your coding.
However: Why not insert an additional field with a simple number sequence or something like that and use that as a primary key? You can ignore this field in your processing if you like, but you still have a pkey. This should work regardless of other problems...
Just an idea.
Regards,
Joerg
10-10-2007 10:24 AM
Hi Pavan,
Now that your requirement is clearly stated, I can say that since you are not having any primary key, you cannot perform any operations such as <b>insert</b> or <b>update</b>. Hope you understand the situation.
You get duplicate keys when you have any primary keys and non-primary keys. <b>MANDT</b> is client-specific. You need to have some Primary key fields depending on your requirement.
Hope this resolves your query.
Regards
Nagaraj
10-10-2007 10:30 AM
hi Pavan,
if MANDT is the only key field in the Ztable, that means that you cannot create more than one record in the table (by client). I think at least the emp_id has to be as key field as well (together with MANDT).
ec
10-10-2007 10:31 AM
10-10-2007 10:32 AM
I think since MANDT is only primary key so always your table contain only one row because when you are trying to insert 2nd row mandt field will be repeating (since sy-mandt is same) and so it will not insert the second row.
so as per my solution make below fields also the key field
emp_id name date proj_id activity_id.
now it will update all the records you have posted in your thread.
regards
shiba dutta
10-10-2007 10:32 AM
Hi Pavan,
Please let me know what are the key fields in your Ztable. Try with below code it may help you. change the "Ztablename" as your database table name and I_INTERNAL TABLE as your internal table name. still you are facing the problem please let me know.
lock the custom table before updating the table.
CALL FUNCTION 'ENQUEUE_E_TABLE'
EXPORTING
MODE_RSTABLE = 'E'
TABNAME = 'ZTABLENAME'
VARKEY =
X_TABNAME = ' '
X_VARKEY = ' '
_SCOPE = '2'
_WAIT = ' '
_COLLECT = ' '
EXCEPTIONS
FOREIGN_LOCK = 1
SYSTEM_FAILURE = 2
OTHERS = 3
.
IF SY-SUBRC <> 0.
MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
ELSE.
INSERT ZTABLENAME FROM TABLE I_INTERNALTABLE ACCEPTING DUPLICATE KEYS.
COMMIT WORK.
ENDIF.
unlock after updating the custom table After updation is done.
CALL FUNCTION 'DEQUEUE_E_TABLE'
EXPORTING
MODE_RSTABLE = 'E'
TABNAME = 'ZTABLENAME'
VARKEY =
X_TABNAME = ' '
X_VARKEY = ' '
_SCOPE = '3'
_SYNCHRON = ' '
_COLLECT = ' '
.
10-11-2007 5:32 AM
Ramakrishna,
My Z-table is as follows.
counter Primary key catscounte char12
empid -- pernr_d numc 8
name -- -- char20
zdate -- catsdate dats8
projid -- --- char10
itemid -- -- char10
actid -- -- char10
Hrs -- catshours quan 4
Remarks -- catsshoortt char 40
Hence the filed "counter" is alone a primary key in the table.
IN se37, i m passing the parameteres and then an internal table will hold all the values.
Then i have coded as follows.
insert <ZTABLE> from table <ITAB> ACCEPTING DUPLICATE KEYS.
commit work.
As of now, the duplicate entries for the same empid are not getting stored in the Ztable.
I would like to store the dupplicate entries in the table.
I hope my problem with this discription is crystal clear......
Kindly help me....
Regards
Pavan
10-10-2007 10:33 AM
10-11-2007 5:36 AM
10-11-2007 5:50 AM
what about the ITAB-COUNTER value it should be unique. Here I am giving you a simple logic.
after filling all the values in itab.
loop at itab.
itab-counter = sy-tabix.
modify itab.
endloop.
now use
insert dbtab from table itab ....
accepting duplicate keys will not insert any duplicate key. it is the way to avoid runtime error if you are trying to insert the duplicate key in database table.
regards
shiba dutta
10-11-2007 5:45 AM
Hi ,
Make Mandt and emp_id as primary keys in your Z table .
This will solve the problem.
Regards,
RK
10-11-2007 5:45 AM
Hi,
Try this..
DATA: v_counter type ztable-counter. " Assuming the counter is the key for the
" ztable
* Get the counter.
SELECT max( counter ) INTO v_counter from ztable.
* If the return code is not successful then there is no entry in the table.
* then set the v_counter to 1.
IF SY-SUBRC <> 0.
v_counter = 1.
ELSE.
v_counter = v_counter + 1.
ENDIF.
* Now process the internal table that has the data.
LOOP AT itab INTO wa.
* Now counter being the key to the table..increment the counter and modify
* the internal table.
wa-counter = v_counter.
* make sure to populate the mandt also.
wa-mandt = sy-mandt. " Current client.
MODIFY itab FROM wa.
* Increment the counter.
v_counter = v_counter + 1.
ENDLOOP.
* Insert the records to the table.
INSERT ztable FROM TABLE itab.
COMMIT WORK.
Thanks
Naren
10-11-2007 5:50 AM
hi,
using either modify or insert key word u can add into database
INSERT (dbtabname) FROM TABLE itab.
rewards if userful
regards
sree
10-11-2007 6:26 AM
Hi All,
Thanks for your kind help.
Now my problem is solved...
Naren, Loads of thanks for your sample code.
The counter logic Rocks now in my Table...!!!!
Regards
Pavan
10-11-2007 6:27 AM