First question - why do you want to duplicate the expiration date for any one Product across any number of Stock records? This is denormalisation and you should only do it if you really, really mean to. (If a Product's expiration date can change over time, you might need to model this differently).
Normally, you would only store the ID of the Product record and go and get the expiration date whenever you needed it.
Assuming you do want to, something like this should do the trick:
insert into Stock
( Quantity_of_the_product -- I'm assiming ID is an auto-incremented value
? -- Stock Quantity being inserted
, ? -- Stock Location being inserted
from Product p
where p.ID_P = ? -- product ID being inserted
Edits from comments:
Products: ID_P, Name, expiration date
Stock: ID, quantity, location, ID_P (foreign key), and expiration date
Read up on Data Normalisation.
Essentially, you want a Single Source of Truth for any data item. If each product has one and only one expiration date, then that value belongs in the Product table. If you need to access that value when looking at Stock items, then you use the product foreign key in the Stock table go and look up that value:
create table products
( ID_P ...
, Name ...
, expiration_date ...
, primary key ( ID_P )
create table stock
( ID ...
, quantity ...
, location ...
, ID_P ...
, primary key ( ID )
, foreign key ID_P references products ( ID_P )
from stock s
inner join products p on s.ID_P = p.ID_P
... is it right ... CREATE TABLE stock (ID, Quantity_of_the_product, product_location_in_the_stock, ID_P foreign key) AS SELECT expiration_date FROM Product
You cannot create a record in the the stock table using [only] a select on the products table because the data values you need to insert into the stock table (like quantity & location) do not exist in the products table. You need to insert each stock record separately, with its quantity and location. As long as you leave the expiration date in the products table (where it [probably] belongs), you don't even need the sub-select:
insert into stock
Of course, you do need to know the product.id of the relevant product!
I'm assuming here that your product.id column is an auto-incrementing numeric field. If that's the case, then the database can generate a value for this field each time you run an insert on this table.
(If not, you'll have to generate and insert that unique value yourself).