11 Replies Latest reply: Oct 5, 2012 5:24 AM by Praveen_1221 RSS

    Insert into

    Chanchal Wankhade
      Dear All,

      Hope you are having greate day.

      Version is.
      BANNER
      ----------------------------------------------------------------
      Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
      PL/SQL Release 10.2.0.4.0 - Production
      CORE    10.2.0.4.0      Production
      TNS for 32-bit Windows: Version 10.2.0.4.0 - Production
      NLSRTL Version 10.2.0.4.0 - Production
      I have 30 tables and on daily basis i am inserting or updating records in these tables. when i insert or update the records i have a column date_updated which set as sysdate.

      Now i have created new 30 dummy tables for above 30 table which will insert data based on condition and the condition will be sysdate.

      condition will be like below :-
      insert into test1 select * from test where date_updated>trunc(sysdate);
      But how can i do it once for all the 30 tables.

      Please suggest me.

      Regards,
      Chanchal Wankhade.
        • 1. Re: Insert into
          Venkadesh Raja
          Create a Trigger
          • 2. Re: Insert into
            952768
            insert into test1 select * from test where date_updated>trunc(sysdate);
            It seems that you want to know which records are updated today.
            Do you want to keep this information stored in several tables?
            Or do you want to create something like a view to check wich records are updated?

            For performance reasons an index on date_updated should be nice.

            Maybe you can explain a little more...
            • 3. Re: Insert into
              Purvesh K
              Chanchal Wankhade wrote:
              insert into test1 select * from test where date_updated>trunc(sysdate);
              Are you Sure, this will insert records into the Dummy table for the current Date? As you say the Date_Updated is set to SYSDATE and how can it be greater than the SYSDATE?

              You should probably be looking for
              Date_updated = trunc(sysdate);
              But how can i do it once for all the 30 tables.

              Please suggest me.
              Is this a One time activity or a Daily Activity?

              One solution is use of Dynamic SQL, however, you should not think of it since you know beforehand the table names etc.

              So, I will suggest you to write 30 Insert statements and execute them.

              If this is a Daily scheduled activity, then you can combine all the 30 Insert statements into a Procedure and call the procedure using a Job created by DBMS_SCHEDULER. Read Here for Examples.
              • 4. Re: Insert into
                952768
                Are you Sure, this will insert records into the Dummy table for the current Date? As you say the Date_Updated is set to SYSDATE and how can it be greater than the SYSDATE?
                trunc(sysdate) will become date started at 00:00:00.

                So every record which will be updated after date 00:00:00 will be selected.
                • 5. Re: Insert into
                  jeneesh
                  Are you looking for Transaction-Level Read Consistency


                  I never had a reason to set isolation level.

                  What is the business logic you are trying to address?

                  Cant you do like below
                  where date_updated between trunc(sysdate) and a_fixed_time_today
                  You can read Serializable Isolation Level also..
                  Edited by: jeneesh on Oct 5, 2012 2:18 PM
                  • 6. Re: Insert into
                    Purvesh K
                    specdev wrote:
                    Are you Sure, this will insert records into the Dummy table for the current Date? As you say the Date_Updated is set to SYSDATE and how can it be greater than the SYSDATE?
                    trunc(sysdate) will become date started at 00:00:00.

                    So every record which will be updated after date 00:00:00 will be selected.
                    Yes, correct.
                    • 7. Re: Insert into
                      Chanchal Wankhade
                      Hi All,

                      As purvesh said how can it be greater then sysdate. I tried select statement like
                      select count(1) from test1 where date_update=trunc(sysdate)
                      It does not show any records or count shows 0.

                      When i query like
                      select count(1) from test1 where date_update>trunc(sysdate)
                      it showing me count. Other Views are wellcome.


                      second thing i will be doing it on daily basis i will truncate the dump table again insert the data into it.



                      should i use procedure?

                      Edited by: Chanchal Wankhade on Oct 5, 2012 2:06 AM
                      • 8. Re: Insert into
                        Purvesh K
                        Chanchal Wankhade wrote:
                        Hi All,

                        When i query like
                        select count(1) from test1 where date_update>trunc(sysdate)
                        it showing me count. Other Views are wellcome.
                        Yes, you will have to use >trunc(sysdate).
                        second thing i will be doing it on daily basis i will truncate the dump table again insert the data into it.
                        What purpose would you meet by Daily truncating the table and populating with new data?
                        • 9. Re: Insert into
                          Chanchal Wankhade
                          Hi purvesh,

                          My original table will hold all the data or you can say data from inception since i am inserting or updating the data on regular basis(This is increamental data).

                          and therefore i can truncate my dummy table i will truncate it regulary and insert the data on the condition mention.


                          How should i do it?
                          • 10. Re: Insert into
                            Purvesh K
                            Chanchal Wankhade wrote:
                            Hi purvesh,

                            My original table will hold all the data or you can say data from inception since i am inserting or updating the data on regular basis(This is increamental data).

                            and therefore i can truncate my dummy table i will truncate it regulary and insert the data on the condition mention.
                            That is the same thing I am trying to Understand. Why are you choosing to Duplicate the Data? It is not a correct approach.

                            If you just want to allow some other user to query the data for a specific duration, then you can use Views.
                            How should i do it?
                            I suggested you one way.

                            1. Incorporate all the Truncate Table (Using Execute Immediate)
                            2. Incorporate all the Insert Into Selects into a Procedure.
                            3. Create a procedure that calls both of these procedures, of course Truncate first and Insert later.
                            4. Create a Job using DBMS_SCHEDULER to execute the Parent Procedure (mentioned in Step 3) every day.
                            • 11. Re: Insert into
                              Praveen_1221
                              If you trying to insert Current dates data
                              try this condition
                              Trunc(Date_Updated) >= Trunc(Sysdate)

                              -- if you want to automate the same in the night/early morning,
                              try this condition
                              Trunc(Date_Update) >= Trunc(Sysdate-1)

                              --Pls ignore this, if you already got solution.


                              Regards,
                              Praveen.