# Massive data insert during migration **URL:** \ **Category:** Rails\ **Created:** [September 14, 2006, 2:20am UTC](https://www.ruby-forum.com/t/massive-data-insert-during-migration/68637 "2006-09-14T02:20:52Z")\ **Posts on this page:** 5\ **Page:** 1
**Author:** ![hotgazpacho](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/h/a587f6/32.png) [@hotgazpacho](https://www.ruby-forum.com/u/hotgazpacho)\ **Post date:** [September 14, 2006, 2:20am UTC](https://www.ruby-forum.com/t/massive-data-insert-during-migration/68637/1 "2006-09-14T02:20:52Z")
Hello all, I’ve got a site that will be used by people (hopefully) all over the world. One of the requirements of registration is country and province of residence. I have a list of all the countries in the world and all their subnational entities. Currently, the two lists are divided between a pair of comma-delimited text files. If this were something less dynamic and cool, I’d probably import it into Excel or some equivalent and then do some find-and-replace voodoo to create the SQL statements to enter it into the database. What would you do if you were doing this in rails and wanted to make sure this data was inserted when you run a migrate task? Will * * * Yahoo! Messenger with Voice. Make PC-to-Phone Calls to the US (and 30+ countries) for 2¢/min or less. ---
**Author:** ![hotgazpacho](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/h/a587f6/32.png) [@hotgazpacho](https://www.ruby-forum.com/u/hotgazpacho)\ **Post date:** [September 14, 2006, 4:08am UTC](https://www.ruby-forum.com/t/massive-data-insert-during-migration/68637/2 "2006-09-14T04:08:59Z")
I’m a rails novice, but I’m a sucker for the least pain approach. This is borderline - for an initial data load this small I would still be tempted to do the Excel voodoo. This information is generally static (apart from major world wars and collapse of national governments), so the tables should be pretty static. If this particular table (or table set) is dynamic enough to require a migration (i.e. columns will change), I would be tempted to look at my designs again for a flaw in the data model. The tool that I would use for an ongoing migration effort or something really sizeable is not publicly available :o( but most RDBMS’s come with fast unload and load utilities that can handle this sort of task, provided the data structure does not change much. Finally, I can actually mount comma delimited files as external tables in the RDBMS that I prefer for rails projects, so my initial import might be to look up how to mount the external table, then “select \* from oldtable into newtable”, or “insert into newtable (col1, col1, …) select (col1, col2, …) from oldtable”, depending on the exact requirements of the new table. I know that this isn’t exactly what you are looking for, but I hope it helps. ---
**Author:** ![hotgazpacho](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/h/a587f6/32.png) [@hotgazpacho](https://www.ruby-forum.com/u/hotgazpacho)\ **Post date:** [September 14, 2006, 4:23am UTC](https://www.ruby-forum.com/t/massive-data-insert-during-migration/68637/3 "2006-09-14T04:23:05Z")
I was thinking along the same lines, but after doing some research, it looks like I could probably use the excel voodoo to generate some fixtures and load the data that way. At least, I think that would work. Can anyone confirm this? removed\_email\_address@domain.invalid wrote: I’m a rails novice, but I’m a sucker for the least pain approach. This is borderline - for an initial data load this small I would still be tempted to do the Excel voodoo. This information is generally static (apart from major world wars and collapse of national governments), so the tables should be pretty static. If this particular table (or table set) is dynamic enough to require a migration (i.e. columns will change), I would be tempted to look at my designs again for a flaw in the data model. The tool that I would use for an ongoing migration effort or something really sizeable is not publicly available :o( but most RDBMS’s come with fast unload and load utilities that can handle this sort of task, provided the data structure does not change much. Finally, I can actually mount comma delimited files as external tables in the RDBMS that I prefer for rails projects, so my initial import might be to look up how to mount the external table, then “select \* from oldtable into newtable”, or “insert into newtable (col1, col1, …) select (col1, col2, …) from oldtable”, depending on the exact requirements of the new table. I know that this isn’t exactly what you are looking for, but I hope it helps. * * * All-new Yahoo! Mail - Fire up a more powerful email and get things done faster. ---
**Author:** ![hotgazpacho](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/h/a587f6/32.png) [@hotgazpacho](https://www.ruby-forum.com/u/hotgazpacho)\ **Post date:** [September 14, 2006, 4:35am UTC](https://www.ruby-forum.com/t/massive-data-insert-during-migration/68637/4 "2006-09-14T04:35:08Z")
I have certainly used Excel as a code generator in the past. ---
**Author:** ![hotgazpacho](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/h/a587f6/32.png) [@hotgazpacho](https://www.ruby-forum.com/u/hotgazpacho)\ **Post date:** [September 14, 2006, 4:55am UTC](https://www.ruby-forum.com/t/massive-data-insert-during-migration/68637/5 "2006-09-14T04:55:11Z")
Will G. wrote: > inserted when you run a migrate task? > > Will > You could convert it to YAML and then import it with your migration > using the ‘migration data dumper’ I wrote a post in this last month: [http://livsey.org/articles/2006/08/15/populating-the-database-with-migrations](http://livsey.org/articles/2006/08/15/populating-the-database-with-migrations) The plugin: [http://blog.tammersaleh.com/articles/2006/08/15/migration-data-dumper-plugin](http://blog.tammersaleh.com/articles/2006/08/15/migration-data-dumper-plugin) hth – Richard L. > **[Livsey.org](http://livsey.org)** > > I’ve been using Firebase with Ember.js quite a lot recently and have just > released Fireplace, a library to integrate the two more easily. It