I currently have a set of SQL queries that run perfect in MS SQL Server that need to be made MySQL compatible. All of them have been converted except a few that are like the following example:
INSERT INTO TABLE1 ( TABLEID, COLUMN_1, COLUMN_2 )
SELECT 2, COLUMN_1, COLUMN_2 FROM TABLE1 WHERE TABLE_ID = 1
From my understanding... MySQL doesnt provide support for such queries and the only way out may be to create an intermediate temporary table using the following technique:
CREATE TABLE TEMPTABLE SELECT 2 AS TABLEID, COLUMN_1, COLUMN_2 FROM TABLE1 WHERE TABLE_ID = 1;
INSERT INTO TABLE1 ( TABLEID, COLUMN_1, COLUMN_2 ) SELECT * FROM TEMPTABLE WHERE AGENCY_ID = 2;
DROP TABLE TEMP;
but I tried running the above set of queries as to execute together as a single ';' seperated string, but this wouldn't work.The following error:
"You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near ';INSERT INTO TABLE1 ( TABLEID, COLUMN_1, COLUMN"
It is pretty obvious from the error that multiple statements cannot be run by executing them as a single command. Can you help me with a work around for the problem?
For your information, the target application uses inline queries.