how to insert data from one table to another [closed]

Question

SELECT a.nid, a.stock, b.qty, sum( b.qty )
FROM uc_product_stock a
INNER JOIN uc_order_products b ON a.nid = b.nid
GROUP BY a.nid

---

the above query give me the following result:

nid stock   qty sum(b.qty)
10  746     1   4
11  256     4   11

---

now i want to insert this in my custom table. For eg, the structure of custom table is:

id | orderIn | orderOut
10  |   746   |   4
11  |   256   |   11

---

Iam using this query, but it gives error:

insert into custom (id, OrderIn, OrderOut) 
select 
a.nid, 
a.stock,
b.qty,
sum(b.qty)
from 
table1 a inner join table2 b on a.id=b.id
group by a.id

Answer 1

You'd use an INSERT..SELECT query like this:

insert into custom (sku, OrderIn, OrderOut) 
select 
a.id, 
a.stock,
sum(b.prod_order)
from 
table1 a inner join table2 b on a.id=b.id
group by a.id

More details at http://dev.mysql.com/doc/refman/5.1/en/insert-select.html

The basic idea is that you build up your select query to define the set of data you want, and then use that as an input to an Insert statement to write it into the table you want to put it in.

In your question you've not defined how the created column is generated, so that isn't covered in this query.

Answer 2

Aashi, you are selecting 4 columns and trying to insert into only 3 columns of another table.