# How to combine SQL queries in self.up

**URL:** <https://www.ruby-forum.com/t/how-to-combine-sql-queries-in-self-up/166758>\
**Category:** Rails\
**Created:** [April 29, 2009, 8:57am UTC](https://www.ruby-forum.com/t/how-to-combine-sql-queries-in-self-up/166758 "2009-04-29T08:57:34Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![Vipin](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/v/edb3f5/32.png) [@Vipin](https://www.ruby-forum.com/u/Vipin)\
**Post date:** [April 29, 2009, 8:57am UTC](https://www.ruby-forum.com/t/how-to-combine-sql-queries-in-self-up/166758/1 "2009-04-29T08:57:34Z")

</div>

Hi

How can i combine multiple SQL statements into one place in self.up in  
a migration.  
For the following, method if i combine 3 SQL statements into one  
section I get the error.

######### following works fine  
def self.up  
execute \<\<-EOF  
CREATE TABLE pks (SNO TINYINT,BUSNO CHAR(20),EXPTIME TIME, PRIMARY  
KEY (SNO));  
EOF  
execute \<\<-EOF  
INSERT INTO pks (SNO,BUSNO,EXPTIME) VALUES(‘1’,‘5L’,080000);  
EOF  
execute \<\<-EOF  
INSERT INTO pks (SNO,BUSNO,EXPTIME) VALUES(‘2’,‘5A’,081500);  
EOF

end

########## it doesn’t work

def self.up  
execute \<\<-EOF  
CREATE TABLE pks (SNO TINYINT,BUSNO CHAR(20),EXPTIME TIME, PRIMARY  
KEY (SNO));  
INSERT INTO pks (SNO,BUSNO,EXPTIME) VALUES(‘1’,‘5L’,080000);  
INSERT INTO pks (SNO,BUSNO,EXPTIME) VALUES(‘2’,‘5A’,081500);  
EOF

end

---

<div class="post-metadata">

**Author:** ![Vipin](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/v/edb3f5/32.png) [@Vipin](https://www.ruby-forum.com/u/Vipin)\
**Post date:** [April 29, 2009, 9:16am UTC](https://www.ruby-forum.com/t/how-to-combine-sql-queries-in-self-up/166758/2 "2009-04-29T09:16:26Z")

</div>

Vipin wrote:

> Hi
> 
> How can i combine multiple SQL statements into one place in self.up in  
> a migration.  
> […]

You shouldn’t need to, at least in the way you’re asking about. Write  
your migrations in Ruby, not raw SQL.

## Best,

Marnen Laibow-Koser  
[http://www.marnen.org](http://www.marnen.org)  
removed\_email\_address@domain.invalid

---

<div class="post-metadata">

**Author:** ![Vipin](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/v/edb3f5/32.png) [@Vipin](https://www.ruby-forum.com/u/Vipin)\
**Post date:** [April 30, 2009, 2:12am UTC](https://www.ruby-forum.com/t/how-to-combine-sql-queries-in-self-up/166758/3 "2009-04-30T02:12:33Z")

</div>

ActiveRecord is available to you. Inside the self.up, you can do:

AnyArbitraryModel.connection.execute(all\_of\_your\_sql)

Any model in your app will do, as you are bypassing your model’s  
business rules and going directly to the connection. The return is a  
Mysql::Result, which you can look up here:  
[http://www.tmtm.org/en/ruby/mysql/](http://www.tmtm.org/en/ruby/mysql/)

Hope this helps.

---

<div class="post-metadata">

**Author:** ![Vipin](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/v/edb3f5/32.png) [@Vipin](https://www.ruby-forum.com/u/Vipin)\
**Post date:** [May 1, 2009, 5:26am UTC](https://www.ruby-forum.com/t/how-to-combine-sql-queries-in-self-up/166758/4 "2009-05-01T05:26:58Z")

</div>

> You shouldn’t need to, at least in the way you’re asking about. Write  
> your migrations in Ruby, not raw SQL.

because there is no other way i guess to have a non integer primary  
key in Rails framework  
–vipin

---

<div class="post-metadata">

**Author:** ![Vipin](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/v/edb3f5/32.png) [@Vipin](https://www.ruby-forum.com/u/Vipin)\
**Post date:** [May 1, 2009, 5:29am UTC](https://www.ruby-forum.com/t/how-to-combine-sql-queries-in-self-up/166758/5 "2009-05-01T05:29:53Z")

</div>

> AnyArbitraryModel.connection.execute(all\_of\_your\_sql)

but in my case, my whole of the data is static and i want to populate  
into the table at the time of migration itself.  
It is going to be like creation of table followed by insertion of  
1000s of rows into them.

Will it work there too?  
If possible can you please explain the syntax of above command in the  
context of my problem.

–vipin

---

<div class="post-metadata">

**Author:** ![Vipin](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/v/edb3f5/32.png) [@Vipin](https://www.ruby-forum.com/u/Vipin)\
**Post date:** [May 1, 2009, 8:50am UTC](https://www.ruby-forum.com/t/how-to-combine-sql-queries-in-self-up/166758/6 "2009-05-01T08:50:07Z")

</div>

Vipin wrote:  
[…]

> because there is no other way i guess to have a non integer primary  
> key in Rails framework  
> –vipin

Be careful! Rails _really_ wants an integer primary key on most tables.  
Although it may be possible to do without it, I understand that problems  
may arise (Fred or other experts, can you confirm this?). My advice  
would be to let Rails put in the key that it wants. It won’t hurt  
anything.

## Best,

Marnen Laibow-Koser  
[http://www.marnen.org](http://www.marnen.org)  
removed\_email\_address@domain.invalid

---

<div class="post-metadata">

**Author:** ![Vipin](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/v/edb3f5/32.png) [@Vipin](https://www.ruby-forum.com/u/Vipin)\
**Post date:** [May 1, 2009, 6:26pm UTC](https://www.ruby-forum.com/t/how-to-combine-sql-queries-in-self-up/166758/7 "2009-05-01T18:26:37Z")

</div>

well…

I assume you’re using a MySQL DB; If that is the case you actually  
need to specify specific options when connecting to the server to  
allow multiple queries in one statement. I’ve honestly never played  
with that before, but  
[http://dev.mysql.com/doc/refman/5.0/en/c-api-multiple-queries.html](http://dev.mysql.com/doc/refman/5.0/en/c-api-multiple-queries.html)  
should be a good start if you still want to go that route.

However, if your main concern is having a non-autoincrementing integer  
as the primary key (which is perfectly legitimate), its not hard.  
Write the migration is Ruby as normal, except also pass :id =\> false  
to the create\_table method. Then run a query to alter table to add the  
primary key (you can also use this to add actual DB constraints and  
foreign keys and what not, although if you do this alot you’re better  
off finding/writing a plugin to do this in a more Rubylike way).  
Finally in your model, use  
set\_primary\_key :name\_of\_primary\_key\_field.

On May 1, 2:50 am, Marnen Laibow-Koser \<rails-mailing-l…@andreas-