ansaurus

Question

Comma separated list of values as different rows, not as fields for one row, for an SQL INSERT

Answer 1

+2 A:

insert into xxx(afield)
select 'a'
union
select 'b'
union
select 'x'

will give you a table like that :

afield

a

b

x

remi bourgarel 2010-04-09 13:54:27

Answer 2

+3 A:

insert into xxx (fields) values (values1), (values2), (values3)

eg insert into mytable (name, desc) values ('name1','desc1'), ('name2','desc2'), ('name3','desc3'), ('name4','desc4')

oedo 2010-04-09 14:00:43

MySQL, PostgreSQL, and MS-SQL-2008 support this kind of multi-row insert. Sorry, I can't remember if Oracle allows it.MS-SQL-2005 and earlier do not, so you have to use remi bourgarel's approach instead (select union select union etc....) which works on everything.In general (with either approach) I would probably avoid doing more than 2000 inserts at a time, especially with the 'select union select union select....' approach. I found it really got slow in groups of 4000 rows on MS-SQL-2000, and so I've been hesitant ever since.

Julius Davies 2010-04-09 18:24:20

Most of the database engines also have non-sql ways to load data directly from files (e.g. CSV). These tend to be much faster, sometimes even 100 times faster if you drop indexes first, but personally I prefer portable SQL, and this is fast enough.

Julius Davies 2010-04-09 18:25:23

ansaurus

tags:

views:

answers:

Comma separated list of values as different rows, not as fields for one row, for an SQL INSERT

afield

related questions