Teiid Designer insert multiple records from a CSV

[kisienya]

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

[rareddy]

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.

[kisienya]

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);

[rareddy]

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

[shawkins]

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

[kisienya]

Thanks Ramesh and Steven.