SQL query construction issue

There two tables:

Table1

field1 | field2

Table2

field1
“string1”
“string2”

I need to insert concatenation of table2.field1 values into table1, so it looks like

insert into table1(field1, field2) values (1, “string1string2”);

How can I do it? Is there any SQL-standard way to do it?

PS: string1 and string2 are values of the field1 column.

PPS:开发者_StackOverflow社区 the main subtask of my question is, how can I get the result of select query into one row? All examples I've seen just use concatenation, but in all your examples SELECT subquery does not return string concatenation for all values of the table2.field1 column.

There is no ANSI standard SQL way to do this.

But in MySQL you can use GROUP_CONCAT

insert into table1 ( field1, field2 )
select 1, group_concat(field1) from table2

In SQL Server 2005 and later you can use XML PATH,

insert into table1 ( field1, field2 )
select 1, (select field1 from table2
           for xml path(''), type).value('.','nvarchar(max)')

In Oracle, you can refer to Stack Overflow question How can I combine multiple rows into a comma-delimited list in Oracle?.

INSERT INTO TABLE1 (FIELD1, FILED2) VALUES (1, CONCAT("string1", "string2"))

try this :

insert into table1(field1, field2)
select table2.field1, table2.string1 || table2.string2 from table2;

You can add a where clause to the query to select only some entries from table2 :

insert into table1(field1, field2)
select table2.field1, table2.string1 || table2.string2 from table2
where table2.field = 'whatever';

I'd try with

insert table1 select field1, string1+string2 from table2

create table #t1 (n int, s varchar(200))
create table #t2 (n int, s1 varchar(100), s2 varchar(100))

insert #t2 values (1, 'one', 'two')    -- worked without into ???
insert #t2 values (2, 'three', 'four') -- worked without into ???
insert #t1 select n, s1+s2 from #t2    -- worked without into ???

select * from #t1

drop table #t1
drop table #t2

After the edit:

No, if you have no way to identify the lines in table2 and sort them the way you want it is impossible. Remember that, in the absence of a order by in the SQL statement, lines can be returned in any order whatsoever

Assuming this is SQL server,

Insert into table1 (field1, field2)
select field1, string1 + string2 
from table2

In oracle you will do it as -

Insert into table1 (field1, field2)
select field1, string1 || string2 
from table2