CODEWITHSUNDEEP
sqlinsert
chathura
chathura

Reputation: 3452

Wrting Sql insert into statement using select statement

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?

Upvotes: 9

Views: 8050

Answers (4)

davek
davek

Reputation: 22925

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.

Upvotes: 7

WhoaItsAFactorial
WhoaItsAFactorial

Reputation: 3558

You are close.

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

Upvotes: 0

Arran
Arran

Reputation: 25076

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

Upvotes: 0

John Woo
John Woo

Reputation: 263933

use INSERT INTO...SELECT statement

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

Upvotes: 2

Related Questions