ad hoc replication for 3 X 40 000 rows

--0-375241091-1256143701=:55963
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable

Hello, I have a task in hand to pull 3 tables of data from a MS SQL db=
and populate a mysql 5.x db.=0ASo I plan to use perl DBI for yank the data=
out of MS SQL and push it into MYSQL.=0AI think I can handle the mysql tab=
le creation and any tweaking that might be required to carry out the initia=
l dataload. However sync's from MS SQL to MYSQL are required every 2=
4 hours after the initial dataload.=0AAnd this has me puzzled.=0AAdvice any=
one?=0APlease ;-) Syd =0A
--0-375241091-1256143701=:55963--

Re: ad hoc replication for 3 X 40 000 rows

On Wednesday 21 October 2009 12:48:21 Sydney Puente wrote:
> Hello,
>
> I have a task in hand to pull 3 tables of data from a MS SQL db and
> populate a mysql 5.x db. So I plan to use perl DBI for yank the data out of
> MS SQL and push it into MYSQL. I think I can handle the mysql table
> creation and any tweaking that might be required to carry out the initial
> dataload.
>
> However sync's from MS SQL to MYSQL are required every 24 hours after the
> initial dataload. And this has me puzzled.
> Advice anyone?
> Please ;-)
>
> Syd


Might be worth checking out an ETL tool (Talend, Pentaho's Kettle) + cron (or
the windows equivalent) to automate this.

kabel

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: ad hoc replication for 3 X 40 000 rows

On Wed, 21 Oct 2009 16:48:21 +0000 (GMT) Sydney Puente said:



> However sync's from MS SQL to MYSQL are required every 24 hours after the initial dataload.
> And this has me puzzled.
> Advice anyone?
> Please ;-)

If there's a auto-inc key, you can find the last one from the
MySQL_table then SELECT ... from MSSQL_table where key > found_key.

Otherwise, add a field to the tables:
seen ENUM('NEW', 'GET', 'GOT') default 'NEW';

At update time:
UPDATE MSSQL_table SET seen='GET' WHERE seen='NEW';
SELECT ... FROM MSSQL_table WHERE seen='GET';
INSERT INTO MySQL_table ...
UPDATE MSSQL_table SET seen='GOT' WHERE seen='GET';

lather, rinse, repeat.


--
Don Read don_read@att.net
It's always darkest before the dawn. So if you are going to
steal the neighbor's newspaper, that's the time to do it.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: ad hoc replication for 3 X 40 000 rows

--0-1869113686-1256203933=:66890
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable

> However sync's from MS SQL to MYSQL are required every 24 hours after t=
he initial=0A> dataload.=0A> And this has me puzzled.=0A> Advice anyone?=0A=
> Please ;-) If there's a auto-inc key, you can find the last one from=
the=0AMySQL_table then SELECT ... from MSSQL_table where key > found_key.=
Otherwise, add a field to the tables:=0Aseen ENUM('NEW', 'GET', 'GOT'=
) default 'NEW'; At update time:=0AUPDATE MSSQL_table SET seen=3D'GET'=
WHERE seen=3D'NEW';=0ASELECT ... FROM MSSQL_table WHERE seen=3D'GET';=0AIN=
SERT INTO MySQL_table ...=0AUPDATE MSSQL_table SET seen=3D'GOT' WHERE seen=
=3D'GET'; lather, rinse, repeat. =0A-- =0ADon Read =
don_read@stripped=0A It's always darkest befo=
re the dawn. So if you are going to=0A steal the neighbor's newspaper, =
that's the time to do it. Thanks very much Don, I think that shows the=
way.=0AHowever I cannot make any changes to the MSSQL db. =0ASo as I only =
have 3 tables I am thinking of populating a 3 shadow tables in the mysql db=
..=0AThe network to the MSSQL db and the MSQSL db itself is unreliable, henc=
e the need to do an extract. Any quick wins spring to mind? (Or gotcha=
s?) Syd =0A
--0-1869113686-1256203933=:66890--