# Inserting multiple rows from array

**URL:** <https://www.ruby-forum.com/t/inserting-multiple-rows-from-array/76712>\
**Category:** Ruby\
**Created:** [November 20, 2006, 5:12pm UTC](https://www.ruby-forum.com/t/inserting-multiple-rows-from-array/76712 "2006-11-20T17:12:10Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![drlelon](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/ee59a6/32.png) [@drlelon](https://www.ruby-forum.com/u/drlelon)\
**Post date:** [November 20, 2006, 5:12pm UTC](https://www.ruby-forum.com/t/inserting-multiple-rows-from-array/76712/1 "2006-11-20T17:12:10Z")

</div>

Hi,

I have two sets of values:

job.id = 1 and contact\_ids = [4, 8]

I want to be able to iterate over all of values in the contact\_id array  
and create database records consisting of a job\_id and contact\_id.

So, in this example, the end result would be two new records that  
contain:

* * *

## |job\_id |contact\_id|

## | 1 | 4 |

## | 1 | 8 |

Any advice?

Thanks,

David

---

<div class="post-metadata">

**Author:** ![drlelon](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/ee59a6/32.png) [@drlelon](https://www.ruby-forum.com/u/drlelon)\
**Post date:** [November 20, 2006, 6:07pm UTC](https://www.ruby-forum.com/t/inserting-multiple-rows-from-array/76712/2 "2006-11-20T18:07:17Z")

</div>

David L. wrote:

> contain:
> 
> * * *
> 
> ## |job\_id |contact\_id|
> 
> ## | 1 | 4 |
> 
> ## | 1 | 8 |
> 
> Any advice?

1. Create an array of records, each a field array containing the job IDs  
and  
the respective contact IDs.

2. Submit the array to the database in a way that the database  
understands.

Did you want specific Ruby code to accomplish this? If so, just say so.

record\_array = []

job\_id = 1

contact\_id.each |id| do  
record\_array \<\< [job\_id, id]  
end

In order to take the next step, we’ll need more information, like, what  
database?

---

<div class="post-metadata">

**Author:** ![drlelon](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/ee59a6/32.png) [@drlelon](https://www.ruby-forum.com/u/drlelon)\
**Post date:** [November 20, 2006, 6:17pm UTC](https://www.ruby-forum.com/t/inserting-multiple-rows-from-array/76712/3 "2006-11-20T18:17:02Z")

</div>

Paul L. wrote:

> Did you want specific Ruby code to accomplish this? If so, just say so.
> 
> record\_array = []
> 
> job\_id = 1
> 
> contact\_id.each |id| do  
> record\_array \<\< [job\_id, id]  
> end
> 
> In order to take the next step, we’ll need more information, like, what  
> database?

A little more code would be helpful. The database is MySQL and the  
table is Assignedjobs.

---

<div class="post-metadata">

**Author:** ![drlelon](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/ee59a6/32.png) [@drlelon](https://www.ruby-forum.com/u/drlelon)\
**Post date:** [November 21, 2006, 7:09am UTC](https://www.ruby-forum.com/t/inserting-multiple-rows-from-array/76712/4 "2006-11-21T07:09:35Z")

</div>

David L. wrote:

> contain:
> 
> * * *
> 
> ## |job\_id |contact\_id|
> 
> ## | 1 | 4 |
> 
> ## | 1 | 8 |
> 
> Any advice?

contact\_ids.map { |each|  
AssignedJob.new({:job\_id =\> job.id, :contact\_id =\> each})  
}.each { |each|  
each.save!  
}

This is ActiveRecord code, or pretty close to it. First, turn the data  
into row objects, then ask each one to save itself.

I hope that helps.

---

<div class="post-metadata">

**Author:** ![drlelon](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/ee59a6/32.png) [@drlelon](https://www.ruby-forum.com/u/drlelon)\
**Post date:** [November 21, 2006, 9:43am UTC](https://www.ruby-forum.com/t/inserting-multiple-rows-from-array/76712/5 "2006-11-21T09:43:49Z")

</div>

contact\_ids.map { |num|  
AssignedJob.create({:job\_id =\> job.id, :contact\_id =\> num})  
}

---

<div class="post-metadata">

**Author:** ![drlelon](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/ee59a6/32.png) [@drlelon](https://www.ruby-forum.com/u/drlelon)\
**Post date:** [November 24, 2006, 5:06am UTC](https://www.ruby-forum.com/t/inserting-multiple-rows-from-array/76712/6 "2006-11-24T05:06:45Z")

</div>

-----BEGIN PGP SIGNED MESSAGE-----  
Hash: SHA1

Joey wrote:

> contact\_ids.map { |num|  
> AssignedJob.create({:job\_id =\> job.id, :contact\_id =\> num})  
> }

ActiveRecord::Extensions (for MySQL) handles bulk inserts using  
multi-value insert statements. It goes up to 40x faster then current  
ActiveRecord behavior.

API looks like:  
MyModel.import( columns, array\_of\_value\_sets, options\_hash )

Documentation on it can be found here:  
[http://www.continuousthinking.com/are/import](http://www.continuousthinking.com/are/import)

Here are some MySQL benchmarks for current ActiveRecord#create vs.  
ActiveRecord::Extensions#import comparing MyISAM to InnoDb to Memory  
tables:  
[http://www.continuousthinking.com/are/import-benchmarks-for-mysql](http://www.continuousthinking.com/are/import-benchmarks-for-mysql)

You can download the latest version from:  
[http://rubyforge.org/projects/arext/](http://rubyforge.org/projects/arext/)

What db adapter do you use? I am currently adding PostgreSQL support  
over this holiday weekend.

Zach

-----BEGIN PGP SIGNATURE-----  
Version: GnuPG v1.4.3 (GNU/Linux)  
Comment: Using GnuPG with Mozilla - [http://enigmail.mozdev.org](http://enigmail.mozdev.org)

iD8DBQFFZnBtMyx0fW1d8G0RAkmxAJ403MJLT6jPjjuawRNBNDVH+y9JNgCfVb+j  
cj0Kxm8B6zkLGZgIOOreMIw=  
=kdbQ  
-----END PGP SIGNATURE-----