7 Replies Latest reply on Dec 21, 2011 6:08 AM by rhlilienkamp

    SQL Help

    kaostika

      Hi, I am wondening, does anyone know the syntax for me to INSERT INTO another table called Report with these SELECT syntax.

       

       

      SELECT PartyID , total

      FROM ILI

      WHERE FinalVersion=1

      and

      Item_ID =5

       

      Oreste

        • 1. Re: SQL Help
          beverly

          Well, Oreste, I guess that might depend upon what tables? Are you inserting into SQL or FMP? and FROM sql or fmp?

          And are the same fields in both files or do you have different fields in the INSERTED table?

           

          Beverly Voth

          • 2. Re: SQL Help
            kaostika

            I am going from FMP to FMP

             

            I have a ILI table and I am inserting into a REPORT table   I have different fields in the insert table.  Hense my trouble getting my vesion to work.

             

            Oreste

            • 3. Re: SQL Help
              beverly

              Well, then. SQL's not going to help you here, unless you are using a Plug-in...

               

              Are you using a Plug-in and if so, which one?

               

              FMP-FMP, you can do several things to get data from one table into another. I tend to use "PUSH" (from the table where I found the correct records). Loop through the found set and push each record to the other table. But that's me.

               

              Beverly

              • 4. Re: SQL Help
                kaostika

                I am using SQL Runner.  The Select command works, but I want to Insert directly into the tale.  Right now I am getting a vaiable and then parsing it out.  That is taking a bit of time.

                 

                Oreste

                • 5. Re: SQL Help
                  rhlilienkamp

                  Try: INSERT INTO Report (Column1, Column2)

                  SELECT PartyID , total

                  FROM ILI

                  WHERE FinalVersion=1

                  and

                  Item_ID =5

                   

                  Replace Column1 & 2 with your field names.

                  • 6. Re: SQL Help
                    kaostika

                    It works  thanks.   One more question.  There are multiple records being returned.  (versions)  Is there a way to get the MAX with this Select Command. 

                    • 7. Re: SQL Help
                      rhlilienkamp

                      Max is one of the Aggregate functions supported.  Use: SELECT SUM (TO.Field ) AS agg FROM TO.  TO is Table Occurance..  Look at Chapter 7 of the ODBC and JDBC Guide.  It is in Eletronic Documentation Folder of English Extras folder.