is it possible to transfer table data from one table to another?

Question

is it possible to copy data from a table to another table?.

here is my example. i have two tables, warehouse and showroom

my warehouse table has (product_id and stock_quantity) and my showroom table has (product_id(fk from warehouse), stock_transferred )..

for example in warehouse table

product_id     stock_quantity
1              10
2              20

how can I transfer product_id(1)with stock_quantity(5) in showroom table and still retain the data in warehouse table?

so after transferring data my warehouse table becomes these:

product_id     stock_quantity
1              5
2              20

and my showroom table becomes these:

product_id     stock_transferred
1              5

how can i do these in a php form?for example i have a text input in which the user can specify how many stocks he will transfer to showroom table.

sorry I cant explain well Im not good in english.

Answer 1

You can use insert into(select...)

Answer 2

BEGIN TRAN 

INSERT INTO showroom
SELECT product_id, @ValueToBeReduced FROM warehouse 
WHERE product_id = 1

-- error handling

UPDATE warehouse 
SET stock_quantity = stock_quantity - @ValueToBeReduced
INNER JOIN showroom
ON warehouse.product_id = showroom.product_id
AND showroom.product_id = 1

-- error handling

COMMIT TRAN