topic Re: Updating database table with DUPLICATE keys in Application Development and Automation Discussions

Updating database table with DUPLICATE keys

Former Member — Wed, 10 Oct 2007 09:18:04 GMT

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

Re: Updating database table with DUPLICATE keys

Former Member — Wed, 10 Oct 2007 09:20:39 GMT

Hi ,

Remove the primary key status of MANDT field.

Regards,

Nivetha.

Re: Updating database table with DUPLICATE keys

Former Member — Wed, 10 Oct 2007 09:22:48 GMT

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

Re: Updating database table with DUPLICATE keys

Former Member — Wed, 10 Oct 2007 09:24:39 GMT

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 insert or update. Hope you understand the situation.

You get duplicate keys when you have any primary keys and non-primary keys. MANDT is client-specific. You need to have some Primary key fields depending on your requirement.

Hope this resolves your query.

Regards

Nagaraj

Re: Updating database table with DUPLICATE keys

JozsefSzikszai — Wed, 10 Oct 2007 09:30:49 GMT

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).

Re: Updating database table with DUPLICATE keys

Former Member — Wed, 10 Oct 2007 09:31:37 GMT

Hi,

Make Emp ID a primary key. that will solve the problem

Re: Updating database table with DUPLICATE keys

Former Member — Wed, 10 Oct 2007 09:32:07 GMT

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

Re: Updating database table with DUPLICATE keys

Former Member — Wed, 10 Oct 2007 09:32:39 GMT

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 = ' '

Re: Updating database table with DUPLICATE keys

Former Member — Wed, 10 Oct 2007 09:33:00 GMT

sorry,

Emp ID & project ID - mark as key fields

Re: Updating database table with DUPLICATE keys

Former Member — Thu, 11 Oct 2007 04:32:13 GMT

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

Re: Updating database table with DUPLICATE keys

Former Member — Thu, 11 Oct 2007 04:36:29 GMT

Still problem continues...

Please Help me..!!

Re: Updating database table with DUPLICATE keys

Former Member — Thu, 11 Oct 2007 04:45:10 GMT

Hi ,

Make Mandt and emp_id as primary keys in your Z table .

This will solve the problem.

Regards,

Re: Updating database table with DUPLICATE keys

Former Member — Thu, 11 Oct 2007 04:45:28 GMT

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

Re: Updating database table with DUPLICATE keys

Former Member — Thu, 11 Oct 2007 04:50:18 GMT

hi,

using either modify or insert key word u can add into database

INSERT (dbtabname) FROM TABLE itab.

rewards if userful

regards

sree

Re: Updating database table with DUPLICATE keys

Former Member — Thu, 11 Oct 2007 04:50:29 GMT

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

Re: Updating database table with DUPLICATE keys

Former Member — Thu, 11 Oct 2007 05:26:34 GMT

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

Re: Updating database table with DUPLICATE keys

Former Member — Thu, 11 Oct 2007 05:27:42 GMT

You are most welcome..:-)

Thanks

Naren