topic Re: Insert data from a dbTABLE into another dbtable in Application Development and Automation Discussions

Insert data from a dbTABLE into another dbtable

luis_rod — Wed, 12 Sep 2018 15:26:56 GMT

Hi all,

(Running Version: 7.00, LVL 17)

We need to insert a lot of data (about 5 million records) from a Z table to another Z table (same structure) in the same system. The target table already exists in the system.

Any easy (and fast) way to do this? All I have been able to find is related to newer versions.

Thanks in davance,

Luis

Re: Insert data from a dbTABLE into another dbtable

pokrakam — Wed, 12 Sep 2018 19:23:26 GMT

INSERT target FROM ( SELECT * FROM source ).

Re: Insert data from a dbTABLE into another dbtable

luis_rod — Wed, 12 Sep 2018 19:39:05 GMT

Mike,

Thanks for your answer. When compiling the program I get:

Field "(" is unknown. It is neither in one of the specified tables nor
defined by a "DATA" statement . . . . . . . . . .

I'm afraid I have a version problem...

Thanks again,

Re: Insert data from a dbTABLE into another dbtable

matt — Wed, 12 Sep 2018 20:08:07 GMT

Try looking at how to do native SQL from ABAP.

Re: Insert data from a dbTABLE into another dbtable

pokrakam — Wed, 12 Sep 2018 20:13:34 GMT

Hmm, I dug out my ABAP book to check and you're right that is not available in 7.00.

OK, but 5M records isn't really a huge amount, have you tried with a simple itab transfer?

SELECT * FROM source INTO TABLE itab.
INSERT target FROM itab.

If the system struggles, break it up:

SELECT * FROM source INTO TABLE itab PACKAGE SIZE 500000. 
  INSERT target FROM itab.
ENDSELECT.

Re: Insert data from a dbTABLE into another dbtable

luis_rod — Wed, 12 Sep 2018 20:15:01 GMT

Matthew,

Had already thought about it. My problem is that I'm getting several errors when running the "native" sql statement. Some of them refer to problems with NULL fields, which I don't realy understand, as the target is an exact copy of the source file. As soon as I wrote a coalesce I began to get 936 errors, which I believe is related to date fields.

Regards,

Luis

Re: Insert data from a dbTABLE into another dbtable

luis_rod — Wed, 12 Sep 2018 20:21:27 GMT

Mike,

That's what I was going to try next. Just wanted to do it with a single INSERT, as I thought it should be a lot more faster and efficient.

Thanks,

Luis

Re: Insert data from a dbTABLE into another dbtable

pokrakam — Wed, 12 Sep 2018 20:42:07 GMT

What was your native SQL statement? Same principle should work.

Re: Insert data from a dbTABLE into another dbtable

matt — Thu, 13 Sep 2018 05:10:21 GMT

If its a one off, efficiency isn't an issue.

But packages of 50000 are default in BW, transferring data from one table to another, so I think it's fairly efficient anyway.

Re: Insert data from a dbTABLE into another dbtable

luis_rod — Thu, 13 Sep 2018 13:44:14 GMT

Matthew,

It's between a one off and once in a (long) while.

I'm somewhat of a newbie in the SAP/ABAP world, and my background is more geared towards IBM's Db2, so my first approach to a problem is to write one heck of SQL statement. I still have to struggle sometimes against the limitations I have with OPEN SQL in our current ABAP version.

Thanks for your help,

Luis.

Re: Insert data from a dbTABLE into another dbtable

matt — Fri, 14 Sep 2018 09:00:59 GMT

It gets much better in later releases. Also check out AMDP and ADBC. 🙂

Re: Insert data from a dbTABLE into another dbtable

luis_rod — Fri, 14 Sep 2018 13:52:06 GMT

(sigh...) One can only hope...:-)

Thanks for the heads up about ADBC and AMDP. The latter looks really nice.

Best Regards,

Luis

Re: Insert data from a dbTABLE into another dbtable

matt — Fri, 14 Sep 2018 14:53:45 GMT

AMDP has its annoyances (especially when it's an HANA Expert Routine in BW). Just found out today that if you want to call a stored procedure from an AMDP, it's only possible if the procedure is in the same name space and the name begins with /BCAMDP/.