# Copy 'has\_many' objects with one SQL INSERT

**URL:** <https://www.ruby-forum.com/t/copy-has-many-objects-with-one-sql-insert/128576>\
**Category:** Rails\
**Created:** [February 8, 2008, 11:57pm UTC](https://www.ruby-forum.com/t/copy-has-many-objects-with-one-sql-insert/128576 "2008-02-08T23:57:32Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![DyingToLearn](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/3d9bf3/32.png) [@DyingToLearn](https://www.ruby-forum.com/u/DyingToLearn)\
**Post date:** [February 8, 2008, 11:57pm UTC](https://www.ruby-forum.com/t/copy-has-many-objects-with-one-sql-insert/128576/1 "2008-02-08T23:57:32Z")

</div>

Hi,

I have the following (this has been simplified to make the question at  
hand more obvious):  
user model has\_many favorites  
favorite model belongs\_to user

After a new user is created, I want to copy all the favorites from an  
existing user to the new user. Right now I have the following code:

```
# bob is the existing user and sam is the new user
bob.favorites.each do | f |
  u = Favorite.new(f.attributes)
  u.user_id = sam.user_id
  u.save!
end

```

This creates an SQL INSERT for every favorite that bob has.

I know SQL could do this in one command with something like:  
INSERT INTO favorites SELECT url, sam.user\_id AS user\_id FROM  
favorites WHERE user\_id = bob.user\_id

Does ActiveRecord have something in place for me to do this? I would  
be nice to keep the code in Ruby rather than SQL.

Thanks in advance.

---

<div class="post-metadata">

**Author:** ![DyingToLearn](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/3d9bf3/32.png) [@DyingToLearn](https://www.ruby-forum.com/u/DyingToLearn)\
**Post date:** [February 9, 2008, 3:10am UTC](https://www.ruby-forum.com/t/copy-has-many-objects-with-one-sql-insert/128576/2 "2008-02-09T03:10:25Z")

</div>

On Feb 9, 2008 9:56 AM, DyingToLearn [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> After a new user is created, I want to copy all the favorites from an  
> existing user to the new user. Right now I have the following code:  
> This creates an SQL INSERT for every favorite that bob has.

Ugh.

> I know SQL could do this in one command with something like:  
> INSERT INTO favorites SELECT url, sam.user\_id AS user\_id FROM  
> favorites WHERE user\_id = bob.user\_id

Yes, that would probably be faster.

> Does ActiveRecord have something in place for me to do this? I would  
> be nice to keep the code in Ruby rather than SQL.

One thing you could try if your DB supports it is wrapping the call in  
a transaction. This can help occasionally. I had a nasty two level  
looping select/insert statement in Rails, wrapped the thing in a begin  
transaction and it went about 20x faster - but this is a bit of a  
hack.

But IMHO don’t be afraid to use SQL directly if it is going to handle  
the problem in a faster and more powerful manner.

ActiveRecord provides some abstraction from the database. But at the  
end of the day, it just runs SQL on the server.

What I do in this situation is make a file in /lib/ and in there  
extend ActiveRecord::Base, call the method what you like, and then put  
your custom SQL in there. That way you can call it from any model if  
you want and when you change databases (as I did one time with this  
method) then you know that all your custom SQL is in this one file and  
it is easy to change.

That’s what I think anyway 🙂

Mikel