5 Replies Latest reply on Jan 30, 2015 3:08 PM by Morgan Kisienya

    Teiid Designer insert multiple records from a CSV

    Morgan Kisienya Newbie

      Hi,

       

      I have successfully managed to insert one record at a time from a CSV into MySQL using Teiid.

       

      I now want to insert multiple records

       

      A snippet of my working SQL, I am calling already existing MySQl procedures at the EXEC part. This works well

       

      #################################################

       

      CREATE VIRTUAL PROCEDURE

      BEGIN

      CREATE VIRTUAL PROCEDURE

      BEGIN

      P5_Union_Model.P5_Union_Proc.ltcPersonID = (SELECT Resident_ViewModel.Resident_Tbl.PatientID FROM Resident_ViewModel.Resident_Tbl WHERE Resident_ViewModel.Resident_Tbl.PatientID = '6000');

       

      DECLARE string VARIABLES.@PatientID = P5_Union_Model.P5_Union_Proc.ltcPersonID;

       

      P5_Union_Model.P5_Union_Proc.FirstName = (SELECT Resident_ViewModel.Resident_Tbl.NameFirst FROM Resident_ViewModel.Resident_Tbl WHERE Resident_ViewModel.Resident_Tbl.PatientID = VARIABLES.@PatientID);

      .

      .

      .

      EXEC P5_Model_Update_Proc.Proc_Resident_Create(VARIABLES.@FirstName, VARIABLES.@LastName, null, VARIABLES.@PrefName, null, null, null, VARIABLES.@DOB, VARIABLES.@P5gender, null, null, null, null, null, 1, 1, 1, null, null, VARIABLES.@IHI, 0);

       

       

      #############################################################

       

      When I want to insert multiple records, I changed the first line to

       

      P5_Union_Model.P5_Union_Proc.ltcPersonID = (SELECT Resident_ViewModel.Resident_Tbl.PatientID FROM Resident_ViewModel.Resident_Tbl WHERE Resident_ViewModel.Resident_Tbl.PatientID IN ('6000', '6001','6002');

       

      But Teiid returns the following error

       

      TEIID30345 The command of this scalar subquery returned more than one value: SELECT Resident_ViewModel.Resident_Tbl.PatientID FROM Resident_ViewModel.Resident_Tbl WHERE A.PatientID IN ('6000', '6001', '6002') LIMIT 2

       

      Any ideas as to how to resolve this

        • 1. Re: Teiid Designer insert multiple records from a CSV
          Ramesh Reddy Master

          Keep your virtual procedure as is for single row. Then use "SELECT INTO .." or "INSERT (..) INTO SELECT .." to update multiple rows in a single scoop.

          • 2. Re: Teiid Designer insert multiple records from a CSV
            Morgan Kisienya Newbie

            Thanks Ramesh,

             

            While I was still waiting for the reply I realized that it will be easier for me to use loop instead of typing in the PatientIDs.

             

            So I have come up with the following

             

            CREATE VIRTUAL PROCEDURE

            BEGIN

                LOOP ON (SELECT Resident_ViewModel.Resident_Tbl.PatientID FROM Resident_ViewModel.Resident_Tbl) AS @PatientID

                BEGIN

                    P5_Union_Model.P5_Union_Proc.FirstName = (SELECT Resident_ViewModel.Resident_Tbl.NameFirst FROM Resident_ViewModel.Resident_Tbl);

                      DECLARE string VARIABLES.@FirstName = P5_Union_Model.P5_Union_Proc.FirstName;

                    CONTINUE;

                END

            END

             

            I get the same error

             

            TEIID30328 Unable to evaluate (SELECT Resident_ViewModel.Resident_Tbl.NameFirst FROM Resident_ViewModel.Resident_Tbl LIMIT 2): TEIID30345 The command of this scalar subquery returned more than one value: SELECT Resident_ViewModel.Resident_Tbl.NameFirst FROM Resident_ViewModel.Resident_Tbl LIMIT 2

             

            What do you mean by use SELECT INTO....Because the VARIABLES.@FirstName together with other variables is used in a  procedure that I have imported into Teiid from MySQL. See the procedure below

             

            EXEC P5_Model_Update_Proc.Proc_Resident_Create(VARIABLES.@FirstName, VARIABLES.@LastName, null, VARIABLES.@PrefName, null, null, null, VARIABLES.@DOB, VARIABLES.@P5gender, null, null, null, null, null, 1, 1, 1, null, null, VARIABLES.@IHI, 0);

            • 3. Re: Teiid Designer insert multiple records from a CSV
              Ramesh Reddy Master

              Creating a VIRTUAL PROCEDURE (VP) Vs VIRTUAL PROCEDURE for Update is little different. That is a VP designed for standalone execution, like what you have done, loop etc. When you define a update procedure on a VIEW then you need to do INSTEAD OF TRIGGER. Here you write the procedure in the context of single row update. The advantage of INSTEAD TRIGGER is you can issue INSERT on VIEW as if it is a physical table. I also realize that you are only working with physical sources now, not VIEWS.

               

              When you looping you need to use LOOP key word, see example here Virtual Procedures - Teiid 8.10 (draft) - Project Documentation Editor

              • 4. Re: Teiid Designer insert multiple records from a CSV
                Steven Hawkins Master

                Morgan,

                 

                The problem is with the statement:

                 

                P5_Union_Model.P5_Union_Proc.FirstName = (SELECT Resident_ViewModel.Resident_Tbl.NameFirst FROM Resident_ViewModel.Resident_Tbl);

                 

                The scalar subquery is returning more than a single firstname.  If you are looping and not in an instead of trigger, then you would need to have this statement use the loop cursor to select a single row.

                 

                Steve


                1 of 1 people found this helpful