SQL query construction issue

Question

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: 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.

Alexandr - did you just vote down 5 answers? Nuts. Did you even read my answer properly before stating "but in all your examples"? — RichardTheKiwi, Mar 17 '11 at 10:57
Richard aka cyberkiwi, unfortunately I cannot check it. And I cannot remove my vote down for your answer, sorry:((( — Alexandr, Mar 17 '11 at 11:02
@Alexandr - It really shows bad attitude to downvote people who are trying to help you when your question was not even clear in first place... — Sachin Shanbhag, Mar 17 '11 at 11:04
Richard aka cyberkiwi, could you pleas edit your answer so i could vote up? — Alexandr, Mar 17 '11 at 11:12
@Alex Richard can't see your comment, use @Richard to send him notification. (or make a comment on his own answer) — Shadow The GPT Wizard, Mar 17 '11 at 12:57
@Shadow Wizard, thank you for the tip. Richard has already updated his answer and I voted it up. — Alexandr, Mar 17 '11 at 13:49
@Alex cheers! What about the other answers? I agree with Sachin that it's not a good idea to globally downvote answers just because they were not accurate enough. — Shadow The GPT Wizard, Mar 17 '11 at 13:57

score 4 · Accepted Answer · edited May 23 '17 at 12:19

4

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?.

edited May 23 '17 at 12:19

Community

1
1

answered Mar 17 '11 at 10:43

RichardTheKiwi

105,798
26
196
262

Looks like somebody has been on a markdown spree... +1 to offset that because you've explicitly stating that there is no Ansi-standard SQL way to do this, and given a couple of examples – Mark Baker Mar 17 '11 at 10:52
thanks for your replay and especially for the last link for Oracle – Alexandr Mar 17 '11 at 13:52

score 0 · Answer 2 · answered Mar 17 '11 at 10:35

0

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

answered Mar 17 '11 at 10:35

Dai

1,510
1
11
12

score 0 · Answer 3 · answered Mar 17 '11 at 10:36

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';

maraguida · Answer 4 · 2011-03-17T10:57:53.350

0

~~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

edited Mar 17 '11 at 10:57

answered Mar 17 '11 at 10:37

maraguida

68
5

2

@downvoter: would you please state your reason fot the downvote. Hopefully I will learn and create better answers in the future. (I've tested that code in my database; and it works for me) – maraguida Mar 17 '11 at 10:54
re: downvote, see this: http://meta.stackexchange.com/questions/83548/revealing-voting-patterns-new-trend – augustin Mar 17 '11 at 15:52

score -1 · Answer 5 · answered Mar 17 '11 at 10:39

-1

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

answered Mar 17 '11 at 10:39

Sachin Shanbhag

54,530
11
89
103

1

Looks like somebody has been on a markdown spree... +1 to offset that because you've indicated that this is different for different databases, and given a couple of examples – Mark Baker Mar 17 '11 at 10:50
It wasn't me, I got hit too though I cannot imagine why. This answer isn't right anyway, since it misread the question - string1 and string2 are **values** – RichardTheKiwi Mar 17 '11 at 10:55
1

@richard - I know that your answer using group_concat(), etc is more accurately answering the precise question... I just got annoyed because the OP simply downvoted every answer he'd been given without comment (even your right answer) – Mark Baker Mar 17 '11 at 11:00
@Richard - I understood what you are saying. yes, you are right. However, can you explain why you are using that `.value('.'` in your answer? – Sachin Shanbhag Mar 17 '11 at 11:02
1

the `.value('.'` is used to handle text than can turn into XML-entities. – RichardTheKiwi Mar 17 '11 at 11:08

SQL query construction issue

5 Answers5