Using (Select * from a table ) to insert data into a table

Question

I have bottom MySql query (sql1).

sq1 = 'select course_id, creator_id, max(course_num) + 1, recordid 
  ' from Courses where recordid in' \
  ' (' + ','.join(map(str, RecordMatch1)) + ') group by recordid'
cursor.execute(sql1)

BTW, RecordMatch1 is an object that has matching data from other previous queries.

I am trying to see if this is possible; (select * from sql1) portion.

sql2 = ' insert into Courses (course_id, creator_id, course_num, record_id) '\
   ' Values ( select * from sql1)'
cursor.execute(sql2)

Or do I have to express everything rather than using (Select * )?

What is best practice?

You don't write `INSERT INTO table1 (col, col, ...) SELECT (col, col, ...) FROM table2` without the word "values". — khelwood, Aug 28 '17 at 17:36

score 2 · Accepted Answer · answered Aug 28 '17 at 17:28

2

You can do this, but you should specify columns in case of schema changes.

answered Aug 28 '17 at 17:28

jonathan.ihm

108
1
1
8

score 1 · Answer 2 · answered Aug 28 '17 at 17:25

1

Just need to confirm you are trying to run a select query and insert its output to a insert query. If that is the case this appears to be good.

answered Aug 28 '17 at 17:25

Daniyal Ahmed

715
5
11

score 1 · Answer 3 · answered Aug 28 '17 at 17:59

1

yes, you can but you should do something like

sql = "SELECT course_id, creator_id, course_num, record_id FROM Courses" 
all = cursor.fetchall()
for i in range(len(all))
    sql1 = "INSERT INTO Courses (course_id, creator_id, course_num, record_id) VALUES (%s, %s, %s, %s)"
    cursor.execute(sql1, (all[i]['Key'], all[i]['Key2'], all[i]['Key3'], all[i]['Key3']))

you can change the select like you want, remember that return a dictionary so take care about the keys, add print(all) to see what happen with the select and see the keys of each column

answered Aug 28 '17 at 17:59

Mauricio Cortazar

4,049
2
17
27

How do I modify areas such as 'Key', 'Key2', 'Key3' listed on the top? – Java Aug 29 '17 at 17:21
comment all the for statement and type print(all[0]) before the for function and tell me – Mauricio Cortazar Aug 29 '17 at 17:39
that should return a dictionary, so you have to refer the key of the value in all[i]['key'] – Mauricio Cortazar Aug 29 '17 at 17:40
I posted a separate question on here [link] (https://stackoverflow.com/questions/45945750/insert-into-table-using-for-in-range-and-keys-of-the-value) – Java Aug 29 '17 at 18:10

Using (Select * from a table ) to insert data into a table

3 Answers3