使用select语句将Srt插入到语句中

I want to insert some values to a table (item1) from a table (item2) and date which is not in that item2 table.

我想从表(item2)中的表(item1)和不在该item2表中的日期插入一些值。

How do I write a SQL insert into statement using the select statement to achieve that?

如何使用select语句将SQL插入语句写入语句来实现?

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?

我正在使用java,今天是一个字符串变量,仍然没有数据插入,什么是错的?

4 个解决方案

#1

you're almost there: just use the SELECT variation for INSERT instread of VALUES, and include your data there as a constant:

你几乎就在那里:只需对VALUES的INSERT instread使用SELECT变体,并将数据作为常量包含在那里:

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

编辑:您必须确保数据类型匹配,即如果您将数据类型设置为日期字段(您希望它!),则必须将其解析为日期。您没有提供数据库的详细信息,但对于SQL Server,它将是这样的

cast('your date in yyyymmdd format' as datetime)

(yyyymmdd always works as it is recognisable ISO format)

(yyyymmdd总是工作,因为它是可识别的ISO格式)

or better still CONVERT

或者更好的还是CONVERT

For MySql you have STR_TO_DATE, for Oracle TO_DATE etc.etc.

对于MySql,您有STR_TO_DATE,对于Oracle TO_DATE等。

#2

use INSERT INTO...SELECT statement

使用INSERT INTO ... SELECT语句

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

#3

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

如果没有您使用的特定SQL服务器/类型,在T-SQL中可以这样做:

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

#4

You are close.

你很亲密

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

#1