1

Im trying to make a Database Migration SQL Script to transfer data from one database to another, the old database has about 10-15 tables the new one has in excess of 300, now im trying to do one big migration i know where eveything needs to go , I just cant figure out how to transfer based on the results.

Heres what ive got at the moment:

SELECT i.item_name AS `item_name` , i.item_rrp  AS `item_rrp` , i.item_web_price  AS `item_price` , p.product_image AS `product_image` FROM `table1`.`items` JOIN(
 `table1`.`items` i , 
 `table1`.`products` p
) WHERE `item_discontinued` = 0

Now with the results from this i want to do an insert , i know how to do the insert but how do I loop over the results from the first query, I can use PHP but it is preferrable for speed that i use complete SQL

apokryfos
  • 38,771
  • 9
  • 70
  • 114
Hudson
  • 397
  • 1
  • 12

1 Answers1

1

No need for a loop, use INSERT AS SELECT :

INSERT INTO <YourTable> (<column names .......>)
SELECT i.item_name AS `item_name` , i.item_rrp  AS `item_rrp` , i.item_web_price  AS `item_price` , p.product_image AS `product_image`
FROM `table1`.`items` JOIN(
`table1`.`items` i , 
`table1`.`products` p
) WHERE `item_discontinued` = 0
sagi
  • 40,026
  • 6
  • 59
  • 84
  • Il give this a try, this will be a very big sql script, its for magento, since their API's arent very slow for mass product conversions – Hudson Apr 12 '16 at 12:42