Using prepared statement in Rails to insert multiple rows

Question

I am currently working on RoR with PostgreSQL.

Basically, ActiveRecord satisfies most of the requirement of data retrieval. However, in some cases, it seems that using one statement would be much more efficient.

Therefore, is it possible to use one prepared statement to do multiple insert as the followings? Or I have to append the raw sql statement myself?

INSERT INTO films (code, title, did, date_prod, kind) VALUES
    ('B6717', 'Tampopo', 110, '1985-02-10', 'Comedy'),
    ('HG120', 'The Dinner Game', 140, DEFAULT, 'Comedy'), ...;

Sachin R · Accepted Answer · 2015-05-21 08:42:18Z

1

try this

user_string = " ('code1','title1', 'aaa', ...), ('code2','title2'...)"

User.connection.insert("INSERT INTO films (code, title, did, date_prod, kind)VALUES"+user_string)

answered May 21, 2015 at 8:42

Sachin R

11.9k11 gold badges37 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Adam Aiken Over a year ago

Thus, I cannot achieve it by prepared statements, right?

Sachin R Over a year ago

Not for multiple rows.

Adam Aiken · Accepted Answer · 2017-10-17 16:01:26Z

1

After two years of experience working on RoR,

There is a gem called activerecord-import that can do the thing. And here is the example code.

books = Array.new
books << Book.new(title: "Programming Ruby", author: "Dave Thomas")
books << Book.new(title: "Agile Web Development with Rails 5", author: "Sam Ruby")
Book.import books

answered Oct 17, 2017 at 16:01

Adam Aiken

4187 silver badges19 bronze badges

Collectives™ on Stack Overflow

Using prepared statement in Rails to insert multiple rows

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related