Insert Statement - ABAP SQL

[Former Member]

Hi,

I do have the following code:

LOOP AT it_table.

PERFORM do_commit.

EXEC SQL.

INSERT INTO emp_group

(employee_groupid,

name,

start_date,

end_date)

VALUES (:it_table-auth_group,

:it_table-auth_group,

to_date(:'20040901', 'YYYYMMDD'),

to_date(:'99991231', 'YYYYMMDD'))

ENDEXEC.

ENDLOOP.

The table IT_TABLE has more than 8000 entries but only like 400 get inserted into EMP_GROUP table. Do commit is a procedure which does commit work every 1000 entries. Does anyone know how to fix this? All the fields in the IT_TABLE are of similar types and I do not know why some get inserted and some dont.

Thanks,

Val.

[Former Member]

specify

COMMIT = 300 or someother desired value.by default it's 1000. if you give it 0 then commit will be done after all rows are inserted.

if you omit it commit is done after 1000 records are inserted and after last row is inserted.

are you sure that there are no duplicate entries in IT_table.

[Former Member]

Hi Sunnil,

Yes there are duplicate entries. Do you think this is the reason?! I tried with 300 and still I get same results.

I do have a lot of duplicate entries as I do have employees who belong to same groups. Is there a way I can insert all the entries even if there are duplicates?

Thanks,

Val.

[Former Member]

if you are entering multiple employees to same groups you should have defined the combination of group and employees as key fields, rather than just the group.

[Former Member]

Hi Val,

I had a similar situation, where I had to stuff multiple entries in the table at one go.

In your case, create a internal table with the same sturcture as that of your database table emp_group and populate all the entries in the internal table.

Then write the following statement:

INSERT  *db_table_name*  FROM  TABLE  *your_internal_table*  ACCEPTING  DUPLICATE  KEYS.

Here is the statement that I have used. gt_insert_rec_list has the same structure as the DB table zln_brk_scrcrd_a

INSERT  zln_brk_scrcrd_a  FROM  TABLE  gt_insert_rec_list  ACCEPTING  DUPLICATE  KEYS.

Hope this helps.

Regards,

Raviraj

[Former Member]

Hi Raviraj,

I tried this but it doesnt work.. remember this is in SQL environment so that might be a reason why this code doesnt work. I will see if there is something similar to your code in SQL. LEt meknow if you have another idea.

Thanks,

Val.

[Former Member]

Hi Val,

Can you try without using the SQL env. ? SAP may actually insert entries correctly.

Regards,

Raviraj

[christine_evans]

>

> Hi,
> 
> I do have the following code:
> 
> LOOP AT it_table.
> 
>       PERFORM do_commit.
> 
>       EXEC SQL.
> 
>         INSERT INTO  emp_group
>                      (employee_groupid,
>                       name,
>                       start_date,
>                       end_date)
> 
>              VALUES  (:it_table-auth_group,
>                       :it_table-auth_group,
>                        to_date(:'20040901', 'YYYYMMDD'),
>                        to_date(:'99991231', 'YYYYMMDD'))
> 
>       ENDEXEC.
> 
>   ENDLOOP.
> 
> The table IT_TABLE has more than 8000 entries but only like 400 get inserted into EMP_GROUP table. Do commit is a procedure which does commit work every 1000 entries. Does anyone know how to fix this? All the fields in the IT_TABLE are of similar types and I do not know why some get inserted and some dont.
> 
> Thanks,
> 
> Val.

Insert looks ok. Should work. You mention duplicates in another post, but if you are trying to insert duplicate keys into a database table then you'd get a duplicate key in index error. Unless you're using a database table which doesn't have a unique key index created, in which case the table must have been created directly in the database, not through SAP. All a bit odd. Which brings me to wondering why you are doing things in this way if you are working in a SAP environment.

[Former Member]

Unfortunately I have to use SQL bacause all the databases I am working on are from another system called Authoria that we use to store our data.. and this system uses SQL. I am there must be some other way that I could fix this.

[christine_evans]

>

> Unfortunately I have to use SQL bacause all the databases I am working on are from another system called Authoria that we use to store our data.. and this system uses SQL. I am there must be some other way that I could fix this.

The table name you were using, EMP_GROUP, seems to be following the naming conventions for one of the SAP standard training tables, which made me think that you were using SAP tables.

This is the sort of thing that you can work out for yourself with a bit of systematic testing.

Have you tried debugging? What happens if you try to insert 10 records? Do they all get inserted into the database table? If not (and I can't see why not), what is wrong with the ones that don't get in? If the insert works for 10, try again with more records until you identify the records that you think are causing the problem.

[Former Member]

Thanks guys,

Again, unfortunately I do not have much control over the table emp_group as it belongs to the client side. When I try to insert only 10 entries I do get the same result.. the entries that seem to be inserted I noticed are those ones which are not duplicated. I can only change tables that are in SAP I do not have any control over the tables that are in SQL environment.

[christine_evans]

>

> Thanks guys,
> 
> Again, unfortunately I do not have much control over the table emp_group as it belongs to the client side. When I try to insert only 10 entries I do get the same result.. the entries that seem to be inserted I noticed are those ones which are not duplicated. I can only change tables that are in SAP I do not have any control over the tables that are in SQL environment.

Does EMP_GROUP have a unique key and a unique index based on that key? This is not compulsory for a table created directly in an Oracle database. If it does have a unique index, then I'm astonished that you are not getting duplicate key in index errors if you are trying to insert duplicates keys; in fact I would bet a small amount of money that such errors are getting generated and that somehow you aren't seeing them. If it doesn't have a unique index, then your data should be being inserted and you should be able to query it back.

You may not have any control over this table yourself, but you certainly need to talk to someone who does have control over it because what you are trying to do is not working. Is this table maintained successfully from anywhere else in your system? If so, find out how that manages to do it.

[Former Member]

This appears to be a problem with the table definition (emp_group). You have to ensure that the primary keys are unique for each entry in your internal table.

Rob