I want to insert some values to a table (item1) from a table (item2) and date which is not in that item2 table.
How do I write a SQL insert into statement using the select statement to achieve that?
eg :
INSERT into item1(date, name, qty)
values(datevalue, select col1, col2 from item2);
This isn't working. What should I do to fix it?
insert into daily_stock(date, product_id, name, weight, qty, free_issues, sales_price,
purchased_price, category, last_purchased_date)
select
**'"+today+"'**,
productId, name, weight, newQty, freeIssues, NewSalesPrice,
NewPurchasePrice, category, datePurchased
from product
I'm using java and today is a string variable , still data is not inserting , whats wrong?
you're almost there: just use the SELECT
variation for INSERT
instread of VALUES
, and include your data there as a constant:
insert into item1(date,name,qty)
select <datevalue>, col1, col2 from item2;
If your date comes from another table, you can do this:
insert into item1(date,name,qty)
select d.dcol1, i2.col1, i2.col2
from item2 i2 inner join table_containing_date_col d
on <some join criteria>
EDIT: you have to ensure that the data types match i.e. your has to be parsable to a date if you are savign it to a date field (which you are, hopefully!). You don't give details of your database but it would be something like this for SQL Server
cast('your date in yyyymmdd format' as datetime)
(yyyymmdd
always works as it is recognisable ISO format)
or better still CONVERT
For MySql you have STR_TO_DATE, for Oracle TO_DATE etc.etc.
use INSERT INTO...SELECT
statement
INSERT INTO item1 (date, name, qty)
SELECT 'value here' as datevalue, col1, col2
FROM item2
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With