inserting multiple records with one sql statement?

Question

Post reply

inserting multiple records with one sql statement?

Blair Dee

SSCommitted

Points: 1648
More actions
July 18, 2005 at 1:23 pm

#363124

is it possible?
i have a form where the user enters several values one per line into a textarea. then i would like to insert a record for each of those items into a db table.
i know i can loop through the values in the textarea and do an insert for each one but that seems like a lot of unnecessary database hits.
so i am wondering if there is a way to insert all the records with one statement.

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Ray M SSC-Insane Points: 21093 More actions · Answer 1

Depends,

Look at this

http://www.bizdatasolutions.com/tsql/sqlarrays.asp

If you would like more help on this specific example,

Please post Table Structures, and Test data.

http://www.aspfaq.com/etiquette.asp?id=5006

Gabor Nyul SSCrazy Eights Points: 9459 More actions · Answer 2

insert into mytable (col1, col2, col3)

select @var11, @var12, @var13

union all

select @var21, @var22, @var23

union all

select @var31, @var32, @var33

...

Bye
Gabor

Blair Dee SSCommitted Points: 1648 More actions · Answer 3

ok so my table consists of 3 fields:

ID - autonumber, PK

ProdID - int, FK

MyValue - varchar 100

i am using ColdFusion to work with this table so if i had 3 values for MyValue that i wanted to insert into 3 new records, is the following correct?

insert into mytable (ProdID, MyValue)

select #url.prodID, #form.value1#

union all

select #url.prodID, #form.value2#

union all

select #url.prodID, #form.value3#

is that how it works?

Gabor Nyul SSCrazy Eights Points: 9459 More actions · Answer 4

if you are constructing a string based on your #url.prodID, #form.value1#

variables then sending that string to the server at once then it looks fine (but don't forget the correct apostophes around the variables while you are constructing your insert statement especially when you are inserting string values.

At the end the constructed insert statement should look like:

insert into mytable (ProdID, MyValue)

select 15, 'MyValue1'

union all

select 2545, 'MyValue2'

union all

select 54455, 'MyValue3'

Bye
Gabor

Blair Dee SSCommitted Points: 1648 More actions · Answer 5

ok so it is sort of working:

my generated query looks like this:

Insert Into option1values(ProdID, myValue)

select 10, 'Red'

union all

select 10, 'Green'

union all

select 10, 'Blue'

union all

select 10, 'Orange'

union all

select 10, 'Purple'

and for some reason when i look into my database (its actually a mysql database) the values entered are only the first 3 characters of each color:

Red

Gre

Blu

Ora

Pur

The interesting thing is that if i put a different value for the first color, the first value from the list is entered into the database in its entirety. for instance if the first color in the list was aqua, the entry in the database woudl be aqua, but all other values in the list (after the first one) are only the first 3 characters.

sushila SSC-Dedicated Points: 35293 More actions · Answer 6

If the data type was char(3) that would make sense...but not if you say that the first entry is stored correctly...

maybe your text fields have a max length set on them ?!

**ASCII stupid question, get a stupid ANSI !!!**

Blair Dee SSCommitted Points: 1648 More actions · Answer 7

well i am using a textarea to collect the list values (one item per line) so there is no maxlength attribute for the a textarea.

and the datatype is definitely varchar(30) - does it need to be char(30) instead?

sushila SSC-Dedicated Points: 35293 More actions · Answer 8

varchar(30) is fine...did you test it by inserting the actual strings - 'aqua', 'blue' etc..or was it a variable ?!

btw...is the prodID the same for all rows ?

**ASCII stupid question, get a stupid ANSI !!!**

Blair Dee SSCommitted Points: 1648 More actions · Answer 9

yeah prodID is the same for all rows. the values being used are variables but i am able to view the actual SQL query beign generated and it is exactly as i pasted above. but i will try it with the actual values and see what happens.

Blair Dee SSCommitted Points: 1648 More actions · Answer 10

ok so i tried insertign the explicit values and it worked fine - so it must be a problem with the variables. i just cant understand what.

Blair Dee SSCommitted Points: 1648 More actions · Answer 11

ok so here's another interesting development - all the values entered into the database are the same length at the first value.

so for instance i entered the following values:

Large

Medium

Small

Extra Large

the values entered into the db table were:

Large

Mediu

Small

Extra

all 5 characters - same as the length of the first value, large.

sushila SSC-Dedicated Points: 35293 More actions · Answer 12

could you pl. post the snippet of code that generates the SQL - incl. variable declaration ?!

Also, how are you parsing your string ? - the length must be getting set in the beginning somewhere...

**ASCII stupid question, get a stupid ANSI !!!**

Blair Dee SSCommitted Points: 1648 More actions · Answer 13

here is my code:

Insert Into option1values(ProdID, myValue)

select #url.prodID#, '#Left(Ltrim(currentvalue),30)#'

union all

</CFIF>

</cfloop>

</CFQUERY>

#form.option1values# is the list submitted from the textarea on the submitting form.

and wheni say #Left(Ltrim(currentvalue),30)#

the Left function cuts off the string at 30 characters.

but the values from the list all have a leading space (except the first value) so the Ltrim function trims off the leading spaces.

I tried doing this without those functions and got the same truncated result.

any ideas?

sushila SSC-Dedicated Points: 35293 More actions · Answer 14

sorry - don't know any cold fusion...

what does "#listlen(form.option1values, chr(13))#" do ?!

**ASCII stupid question, get a stupid ANSI !!!**