troubleshooting Question

How tocopy & insert many rows into a mysql table from anothre tablewith one SQL statemnt

Avatar of venks
venks asked on
17 Comments1 Solution1875 ViewsLast Modified:
I have two tables table1 & table2 in two different mySQL databases

I have 3  columns  col11 col12  & col13 in table1 & 

I have 3  columns col21 col22 & col33  in table 2.

Bothe the first columns ie  col11 & col22 are auto incrementing primary key fields

Col12 col 13  of Table1 & col22 & col23 of Table2  are  'Not Null'  fields

Table1 has large amount of data(in thousands) & table2 is empty

How do i copy the contents of col12 & col13 of table1 to col22 & col23  of table2  with one SQL statemnt since speed of execution is important & no loops are allowed

I thought i will write  the following code in python

import MySQLdb

connection = MySQLdb.connect(host='localhost', db='CNS' ,user= 'root',passwd='')
cursor = connection.cursor()

cursor.execute(" SELECT col12,col13   FROM table1)

resultFromTable1OftwoColumns = cursor.fetchall()

connection = MySQLdb.connect(host='localhost', db='MPT' ,user= 'root',passwd='')
cursor = connection.cursor()

cursor.execute(" INSERT INTO table2  (col21,col22,col23) VALUES (s%,%s,%s) " , ('',resultFromTable1OftwoColumnst)

 I  know the  line

cursor.execute(" INSERT INTO table2  (col21,col22,col23) VALUES (s%,%s,%s) " , ('',resultFromTable1OftwoColumnst)

isi not of correct syntax.
What i was planning was to get the tuple value of resultFromTable1OftwoColumnst & pass it to the two fields of table2 through Insert statment

How should the insert statement be rewritten so that it works.Does this also require other statemnets to be rewritten ?

Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 17 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 17 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros