0

Consider this simple query

Query

INSERT INTO NewCourses (name, location, gid) from
SELECT name, location, 1
FROM   courses

which does nothing but insert all records from table courses to table NewCourses .

But if I would like to return this same select statement what will I do? Without this I need to run this insert statement then again run the same select statement like

INSERT INTO NewCourses (name, location, gid) from
SELECT name, location, 1
FROM   courses

//THIS IS THE WASTE OF TIME
SELECT name, location, 1
FROM   courses

Please let me know if it is possible?

TAHA SULTAN TEMURI
  • 4,031
  • 2
  • 40
  • 66
  • 5
    Your method is fine. `INSERT` statements don't return results. `SELECT` statements don't modify the database. That is how SQL is designed. – Gordon Linoff Oct 08 '19 at 13:12
  • ok then wont it be wastage? especially if select query takes too long? – TAHA SULTAN TEMURI Oct 08 '19 at 13:12
  • *"ok then wont it be wastage? especially if select query takes too long? "* Sounds like you want to switch to PostgreSQL... `INSERT INTO NewCourses ... RETURING name, location, 1` .. Anyhow also don't expect it do be fast there if `SELECT name, location, 1 FROM courses` needs to copy great data sizes also in PostgreSQL it is also meant as syntax sugar that you don't have to write a separate SELECT.. – Raymond Nijland Oct 08 '19 at 13:13
  • @TAHASULTANTEMURI . . . Really, you should select the values from `NewCourses`. Perhaps you want to save the results in a *temporary* table, insert the temporary table into `NewCourses` and then select from that. If the query is complicated, this is probably the best solution. – Gordon Linoff Oct 08 '19 at 13:16
  • Maybe it depends on datatypes and if MySQL can handle that temporary table in memory (settings wise).. @GordonLinoff .. otherwise it will be more wastefull then the topicstarters queries with Disk I/O as the temporary table needs to be converted from ram to disk.. – Raymond Nijland Oct 08 '19 at 13:20
  • [MySQL: Get a return result from an INSERT SELECT](https://stackoverflow.com/questions/13067121/mysql-get-a-return-result-from-an-insert-select) – Abra Oct 08 '19 at 13:23
  • https://stackoverflow.com/questions/8479315/sql-inserting-a-row-and-returning-primary-key – ChatterOne Oct 08 '19 at 13:24
  • If you really want to save a roundtrip, you can wrap that two queries into a stored procedure and call that SP in your code – PeterHe Oct 08 '19 at 13:46
  • I am already calling it from SP – TAHA SULTAN TEMURI Oct 08 '19 at 13:56
  • *"If you really want to save a roundtrip, you can wrap that two queries into a stored procedure and call that SP in your code "*@PeterHe the stored procedure in this case would most likely be equal or use more overhead then running a separared `INSERT / SELECT` as a stored procedure statements use up more memory and most likely needs the used memory longer before the resources are freed on the MySQL server... Using a stored procedure in this case is not really justifiable in my opinion ... – Raymond Nijland Oct 08 '19 at 14:34

0 Answers0