Insert data from a dbTABLE into another dbtable

[luis_rod]

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

[pokrakam]

INSERT target FROM ( SELECT * FROM source ).

[luis_rod]

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,

[matt]

Try looking at how to do native SQL from ABAP.

[pokrakam]

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.

[luis_rod]

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

[luis_rod]

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

[pokrakam]

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

[matt]

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.

[luis_rod]

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.

[matt]

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

[luis_rod]

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

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

Best Regards,

Luis

[matt]

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