8 Replies Latest reply on Dec 5, 2014 1:37 AM by kisienya

    Insert data from SQL to MySql using Teiid

    kisienya

      Hi,

       

      Can someone point me to documentation or give me pointers on how to insert data to a MySQL table from SQL table using Teiid designer?

       

      Cheers

        • 1. Re: Insert data from SQL to MySql using Teiid
          kisienya

          OR insert data to a MySQL table from CSV using Teiid. I already have the JDBC and flat-file connections in place and I can preview data from both tables in Teiid designer.

          • 2. Re: Insert data from SQL to MySql using Teiid
            shawkins

            Assuming that you already have the usage of TEXTTABLE in a view, then you would just use standard sql:

             

            insert into mysqltable (column list) select columns from textview

            • 3. Re: Insert data from SQL to MySql using Teiid
              kisienya

              Much appreciated Stephen.

              This is what I have:

              1. A relational view model holding a table that has fields of interest (fields that hold records from two tables -MySQL and textfile-.

              2. A select query on select tab - which works well when i hit preview data button

              SELECT field1, field2

              FROM table1

              UNION ALL

              SELECT filed1, field2

              FROM table2

              3. On insert tab, I have

              CREATE VIRTUAL PROCEDURE

              BEGIN

              INSERT INTO table1 (field1,field2)

              SELECT field1,field2

              FROM table2

              WHERE ......

              No 3 has no errors

              After the above, I don't know where to go to, in Teiid designer, to have the data inserted into MySQL

              • 4. Re: Insert data from SQL to MySql using Teiid
                rareddy

                Morgan,

                 

                Once you deploy the VDB built with above view to a Teiid server, you can either use external JDBC clients like SquirreL or Teiid Designer data tools (right click on vdb, execute) then type a insert command

                 

                insert into view (...) values (...)


                Ramesh..

                1 of 1 people found this helpful
                • 5. Re: Insert data from SQL to MySql using Teiid
                  kisienya

                  Thanks Ramesh, let me try it out.

                  • 6. Re: Insert data from SQL to MySql using Teiid
                    kisienya

                    Hi

                     

                    I get the error below when i execute an insert query on VDB...any ideas....

                     

                    TEIID30504 Remote org.teiid.core.TeiidProcessingException: TEIID30504 Model_ltcperson: 1366 TEIID11013:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: INSERT INTO `ltcdb_prod`.`ltcperson` (`ltcPersonID`, `FirstName`, `LastName`, `CreatedBy`, `CreatedDate`) VALUES ('9000', 'Usert', 'Testt', 'Morgan', '02122014')]

                     

                    Cheers

                    • 7. Re: Insert data from SQL to MySql using Teiid
                      shawkins

                      There should be nested exception for this that could provide more detail. What we can infer from just that is you are seeing a mysql error 1366 - which is an incorrect string value.  You'll want to ensure that what you are trying to insert are valid values.

                      • 8. Re: Insert data from SQL to MySql using Teiid
                        kisienya

                        Thanks Stephen and Ramesh.

                         

                        I was able to insert data after trying on a local scaled down database as opposed to a remote MySQL complex MySQL.

                         

                        Could be connection problems or validation issues.

                         

                        i will find out.