Wrting Sql insert into statement using select statement

Question

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?

http://stackoverflow.com/questions/224732/sql-update-from-one-table-to-another-based-on-a-id-match — Albin N, Oct 29 '12 at 11:28

davek · Accepted Answer · 2012-10-29T13:19:23.633

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.

funny... I never *ever* code the keyword `inner`, since it's the default join type in every database and everyone knows that. — Bohemian, Oct 29 '12 at 11:31

score 2 · Answer 2 · answered Oct 29 '12 at 11:28

2

use INSERT INTO...SELECT statement

INSERT INTO item1 (date, name, qty) 
SELECT 'value here' as datevalue, col1, col2 
FROM   item2

answered Oct 29 '12 at 11:28

John Woo

258,903
69
498
492

score 0 · Answer 3 · answered Oct 29 '12 at 11:28

0

In the absence of a specific SQL server/type you are using, in T-SQL this can be done like so:

INSERT INTO Table1
SELECT '29/01/2012', T2.Column1, T2.Column2 FROM Table2 T2

answered Oct 29 '12 at 11:28

Arran

24,648
6
68
78

score 0 · Answer 4 · edited Oct 29 '12 at 11:35

0

You are close.

INSERT into ITEM1 (date, name, qty) 
values (datevalue, (SELECT col1, col2 FROM item2));

edited Oct 29 '12 at 11:35

davek

22,499
9
75
95

answered Oct 29 '12 at 11:29

WhoaItsAFactorial

3,538
4
28
45

Wrting Sql insert into statement using select statement

4 Answers4