# Using ruby to transfer and clean data between databases

**URL:** <https://www.ruby-forum.com/t/using-ruby-to-transfer-and-clean-data-between-databases/118174>\
**Category:** Ruby\
**Created:** [October 24, 2007, 2:12pm UTC](https://www.ruby-forum.com/t/using-ruby-to-transfer-and-clean-data-between-databases/118174 "2007-10-24T14:12:17Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![runrunshaw](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/b5ac83/32.png) [@runrunshaw](https://www.ruby-forum.com/u/runrunshaw)\
**Post date:** [October 24, 2007, 2:12pm UTC](https://www.ruby-forum.com/t/using-ruby-to-transfer-and-clean-data-between-databases/118174/1 "2007-10-24T14:12:17Z")

</div>

Hi all,

I have some data in an access database and I want to move this to my new  
SqlServer2005 database. However the schema of the database has changed  
significantly, it’s been normalised and various tables have been added,  
so the data won’t fit in exactly without first being ‘cleaned’ so to  
speak.

I was just wondering whether you think I could write some kind of ruby  
script to automate some of it, whether anyone has done something  
similar, or if there’s a better way of doing this.

Fairly open question I know, but I want to know how others would go  
about doing this before I embark upon it.

Cheers,  
Daf

---

<div class="post-metadata">

**Author:** ![runrunshaw](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/b5ac83/32.png) [@runrunshaw](https://www.ruby-forum.com/u/runrunshaw)\
**Post date:** [October 24, 2007, 2:52pm UTC](https://www.ruby-forum.com/t/using-ruby-to-transfer-and-clean-data-between-databases/118174/2 "2007-10-24T14:52:37Z")

</div>

> this to my new  
> Fairly open question I know, but I want to know how others would go  
> about doing this before I embark upon it.
> 
> ## Cheers, Daf
> 
> Posted via [http://www.ruby-forum.com/](http://www.ruby-forum.com/).

That seems more like a job for MS SQL Data Transformation Services.  
While  
you could certainly just select row by row from Ruby, build the new  
datastructure and insert it, MS SQL should come with all the tools  
necessary  
built in unless you have somewhat strange or outrageous transformation  
requirements.

HTH,

Felix

---

<div class="post-metadata">

**Author:** ![runrunshaw](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/b5ac83/32.png) [@runrunshaw](https://www.ruby-forum.com/u/runrunshaw)\
**Post date:** [October 24, 2007, 4:29pm UTC](https://www.ruby-forum.com/t/using-ruby-to-transfer-and-clean-data-between-databases/118174/3 "2007-10-24T16:29:14Z")

</div>

2007/10/24, Felix W. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid):

> > this to my new  
> > Fairly open question I know, but I want to know how others would go  
> > datastructure and insert it, MS SQL should come with all the tools necessary  
> > built in unless you have somewhat strange or outrageous transformation  
> > requirements.

I was going to write the same. Additional info: DTS allows you to do  
arbitrary transformations using a scripting language. Last time I  
looked JavaScript and VisualBasic were supported. Since this is  
integrated into DTS I’d say this is probably a better choice.

Kind regards

robert

---

<div class="post-metadata">

**Author:** ![runrunshaw](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/b5ac83/32.png) [@runrunshaw](https://www.ruby-forum.com/u/runrunshaw)\
**Post date:** [October 24, 2007, 4:47pm UTC](https://www.ruby-forum.com/t/using-ruby-to-transfer-and-clean-data-between-databases/118174/4 "2007-10-24T16:47:13Z")

</div>

Ok, I think i’ll be looking into doing that then. I believe DTS is  
called ‘Integration Services’ in SQL server 2005 correct? I think I’ll  
need to install it off the cd as I can’t find it in it’s start group.

Thanks for the help.  
Daf