Database Design

Inserting an array into a table

Please support our Database Design advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

Thread Solved

•

Join Date: Jan 2008

Posts: 54

Reputation: Xessa is an unknown quantity at this point

Solved Threads: 0

Xessa

Offline

Junior Poster in Training

Inserting an array into a table

Jun 5th, 2009

I want to insert an array into a table.

Each array element is a row in the table. How can I achieve my goal?

I don't want to use

Help with Code Tags

(Toggle Plain Text)

 VALUES x1,x2,x3,x4 ......

Last edited by Xessa; Jun 5th, 2009 at 6:12 am.

•

Join Date: Oct 2005

Posts: 93

Reputation: humbug is an unknown quantity at this point

Solved Threads: 13

humbug

Offline

Junior Poster in Training

Re: Inserting an array into a table

Jun 7th, 2009

You can use a for loop to operate on each element of the array. I'm assuming it's a one (meaningful) columned table.

 Help with Code Tags 
 c++ Syntax (Toggle Plain Text) 
//len is length of array
for (i=0; 0<len; i++){
  //create SQL statement
  sql="INSERT INTO table (row1) VALUES " . array[i];
  //[execute sql here]
}
//len is length of array
for (i=0; 0<len; i++){
  //create SQL statement
  sql="INSERT INTO table (row1) VALUES " . array[i];
  //[execute sql here]
}

Alternatively, you could do it like so, if it will work with your version of SQL:

 Help with Code Tags 
 c++ Syntax (Toggle Plain Text) 
//all inserts are done in the one SQL statement
 
int len = //length of array
//the start of the statement. The first element is done here because
//one less comma is required then the number of elements
string sql = "INSERT INTO  table (row1) VALUES (" . array[0];
for (int i=1; 0<len; i++){
  //create SQL statement
  sql .= "), VALUES (" . array[i];
}
sql .= ");";
//[execute sql here]
//all inserts are done in the one SQL statement

int len = //length of array
//the start of the statement. The first element is done here because
//one less comma is required then the number of elements
string sql = "INSERT INTO  table (row1) VALUES (" . array[0];
for (int i=1; 0<len; i++){
  //create SQL statement
  sql .= "), VALUES (" . array[i];
}
sql .= ");";
//[execute sql here]

http://en.wikipedia.org/wiki/Insert_(SQL)

Last edited by humbug; Jun 7th, 2009 at 12:27 am. Reason: found better solution

★ "If your not having fun, your doing something wrong." - Humbug
★ Did I help you out? Did I piss you off? Add to my reputation!
★ The Gabriel Method is a great book for losing weight and keeping healthy - I know Jon Gabriel Personally.