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
tested with MSSQL Server 2008
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
精彩评论