SELECT Statement

[Former Member]

Guys,

so I would like to insert some data from one table to another one based on its calender day (Date). Is this correct statement?

Yesterday = Sy-Datum - 1

SELECT * from SOURCETABLE into DESTINATIONTABLE WHERE CALDAY = Yesterday.

ENDSELECT.

I can see some records when I debug but then the new table stays empty. Is there a reason why?

Thanks,

RG

[Former Member]

Are you trying to put the data into a database table or an internal table.

Rob

[amit_khare]

SELECT * from SOURCETABLE into TABLE DESTINATIONTABLE WHERE CALDAY = Yesterday

[former_member191735]

Make sure you the source table is DB table and use into statement before target table check the data in table (SE16) for the date you have specified and define Yesterday type CALDAY

[nkr1shna]

Hi Ram,

My suggestion here is to replace SELECT.... ENDSELECT construct and code with SELECT.... INTO TABLE construct which can resolve your issue.

Best Regards,

Krishna

[Former Member]

Guys,

This is not an internal table. It is a database table that I created which will keep physical data and retain the data therefore, I don't think

SELECT * from SOURCETABLE into TABLE DESTINATIONTABLE WHERE CALDAY = Yesterday

will work as it is not an internal table.

Thanks,

RG

[Former Member]

Hi Ram,

In order to Insert data into a database table you need to use the INSERT statement.

First SELECT data from your SOURCE table into an Internal Table of the same type -

SELECT * FROM <source_table> INTO TABLE <internal_table> WHERE CALDAY = yesterday.

Now your Internal Table has all the data from SOURCE table where CALDAY is yesterday.

Insert this data into your DESTINATION table using INSERT statement -

INSERT <destination_table> FROM TABLE <internal_table>.

Regards,

Himanshu

[Former Member]

.

[Former Member]

hi,

Using the Select statement you cannot update the database table ....for this you need to select the data in to internal table and using the UPDATE statement update the DB table..

ITAB will be internal table of type DESTINATIONTABLE.

SELECT * from SOURCETABLE into TABLE ITAB WHERE CALDAY = Yesterday.

UPDATE DESTINATIONTABLE FROM TABLE ITAB.

[Former Member]

Hi,

It is not permitted by SAP to update a database table directly using sql statement. Instead you have to use some other techniques like BAPI to update the same.

Using select query we cannot directly update a database table. Instead fetch the relevant records to an internal table and then using UPDATE statement, you can update the database table. check the link below for your reference.

http://www.abapcode.info/2007/12/updating-database-tables-sql-query.html

Regards,

John

[Former Member]

Hi,

You can not update the database table by using SELECT statement

1. Select the data into ITAB by using SELECT statement.

2. Use update statement to update the database table.

SELECT * FROM SOURCETABLE INTO TABLE DESTINATION WHERE CALDAY = Yesterday.

UPDATE DESTINATIONTABLE FROM TABLE ITAB.

Regards,

Jyothi CH.

[former_member222860]

Hi,

u can try with native sql.

just like this:

EXEC SQL.

INSERT  INTO vbak_2 SELECT * FROM VBAK_1 

ENDEXEC

regards\

Mahesh

[Former Member]

Hi Ram

You are not allowed to update the database table directly through a SQL query.

Please use:

SELECT * from SOURCETABLE into TABLE itab WHERE CALDAY = Yesterday.

UPDATE DESTINATIONTABLE from TABLE itab.

Moreover, the above statement will enable you to remove the ENDSELECT statement and will thus enhance your o/p performance.

[Former Member]

I did as you guys suggested. I can see the data coming into Internal Table but then it does not seem update my target table. Here is my code.

TABLES: /BI0/PCOSTCENTER, ZMyCTR.

DATA: it_Cost_CTR like /BI0/PCOSTCENTER occurs 0,

wa_Cost_CTR like /BI0/PCOSTCENTER.

DATA: YESTERDAY TYPE d.

SEVENDAYS = SY-DATUM - 1.

SELECT * from /BI0/PCOSTCENTER into wa_Cost_CTR WHERE CALDAY = YESTERDAY.

Append wa_Cost_CTR to it_Cost_CTR.

UPDATE ZMyCTR from TABLE it_Cost_CTR.

ENDSELECT.

Anything wrong with it???

Thanks,

RG

[Former Member]

>

> Anything wrong with it???

Yes - you're confusing internal tables with the work area.

Rob

[Former Member]

Hi,

Don't use the update statment, instead use the modify statment.

MODIFY  dbtab      FROM TABLE itab

And make sure you keep that statement outside the endselect. I would rather suggest you directly select the data into the internal table using select ....into table see below

SELECT * from /BI0/PCOSTCENTER into table it_Cost_CTR WHERE CALDAY = YESTERDAY.

modify ZMyCTR from TABLE it_Cost_CTR

Now modify will create a record if it doesn't exist with the key fields of the Z table and update otherwise.

regards,

Advait

[Former Member]

I want to say thanks to everyone who gave me their inputs and especially Advait. I was able to fix the issue by using Modify statement. I wish I could give 6 pts to more than just 2 people.

Thanks again guys,

RG

[Former Member]

You might have been able to SELECT directly into the database table using a subquery, but I really don't know if it would work.

Rob