Newbie Help

[michaelwilding]

Hi everyone. I am very new to databasing and MySQL and am hoping someone here may be able to help me. I am creating a horse racing database and I have created my database and table structure which I am happy with, all the indexes and foreign keys seem to be working fine. I now need to import the data, the data comes from an SQL Server database that has a different table structure which means that I need to put into different tables (each table in the SQL Server database has data that feeds into numerous different tables in my database) and I am not sure how to do this? I also have the problem below:

I import all the horses in the database and my MySQL database creates a unique identifier for each horse. I then want to import the past races a horse has been in, this table in the SQL Server database uses an ID to identify the horse. Obviously my ID is different to the ID in the SQL Server database. How do I import the data with the database recognising that it needs to find this horse from my database and use its MySQL unique identifier?

My last question is that i have a number of fields that need to store a piece of information that is calculated by looking at figures in other fields in different tables. Sums are performed on these figures and the result is entered into the field we are looking at. Where do I go in order to program these calculations?

Thank you in advance for any help.

M.

[peterlaursen]

1)

With the “Imort External Data Tool” there is an option to spefiy a query. With this you will be able to rearrange columns.

If you have in SQL Server

table a (columns b,c)

table d (columns e,f)

and in MySQL

table p (columns r,s,t)

You can do something like

SELECT a.b AS r, a.c AS s, (a.e + a.f) AS t FROM thetable

for instance. Note that the SQL syntax must follow rules defined by the SOURCE (SQL Server in the case)

2) Last question: not really possible to tell what is best option. We need to know more details. An UPDATE TRIGGER is an option – but I do not like TRIGGERS much myself (mostly because in MySQL you cannot turn them off when you don't want them). You may use a MySQL EVENT (from MySQL 5.1) or a SQLyog/SJA notifications services job as well.

[michaelwilding]

That is brilliant thank you. Makes perfect sense. How would I get it to find the relevant ID's for the fields instead of the ID's used in the SQL Server database?

What details would you need to know for question 2? A basic example would be a field that:

Looks at the horses ID and finds all the races the horse ran in, finds a rating, calculates the average and puts this figure into a field in another table. I would like to store the actual figure in the field rather than the calculation to help with speed.

[peterlaursen]

1) only you can know column names in SQL Server and MySQL respectively and how they shall be remapped/rearranged. (if I understand)

2) basically you will have have to understand the possible options yourself. However in this particular case I think a TRIGGER would be OK.

[michaelwilding]

Hi,

I am just trying this now and ran into a problem. I am putting the Race Header tables into MySQL. I already have my courses table in MySQL filled with their own ID for each course.

I import the information as you said below, but when it comes to the Race Header I want to import the courses information for each row in the RaceHeader table. In the SQL Server database this is an ID that links to the SQL Server courses table. I of course want to put in an ID that links to the MySQL courses table. How do i go about doing this?

Thank you,

Michael

peterlaursen wrote on Jul 28 2009, 06:29 PM:

1)

With the “Imort External Data Tool” there is an option to spefiy a query. With this you will be able to rearrange columns.

If you have in SQL Server

table a (columns b,c)

table d (columns e,f)

and in MySQL

table p (columns r,s,t)

You can do something like

SELECT a.b AS r, a.c AS s, (a.e + a.f) AS t FROM thetable

for instance. Note that the SQL syntax must follow rules defined by the SOURCE (SQL Server in the case)

2) Last question: not really possible to tell what is best option. We need to know more details. An UPDATE TRIGGER is an option – but I do not like TRIGGERS much myself (mostly because in MySQL you cannot turn them off when you don't want them). You may use a MySQL EVENT (from MySQL 5.1) or a SQLyog/SJA notifications services job as well.

[peterlaursen]

We cannot understand exact details of what you want. There are too many words and too little SQL! Also we do not provide consultancies for application development – this is beyond normal product support.

However if you can provide a test case like this:

1) a database to import from (for the test case an Access database would be preferable)

2) structure of table you import to. Please SHOW CREATE TABLE (not DESCRIBE)

3) what it should look like after import (SQL dump)

.. we could look into it. But please keep the test case small – a few rows of data are enough.

But I also never told that you would not need to adjust your workflow or table a little to be able to use our tool!

[michaelwilding]

Hi Peter,

Thank you for the reply and help. Being completely new to databases I am finding your tool very helpful and of course would not expect you to help me with the application development. I am battling it forwards at the moment but shall post some SQL if I get really stuck. Thank you again for the help so far.

Michael

peterlaursen wrote on Jul 30 2009, 09:17 AM:

We cannot understand exact details of what you want. There are too many words and too little SQL! Also we do not provide consultancies for application development – this is beyond normal product support.

However if you can provide a test case like this:

1) a database to import from (for the test case an Access database would be preferable)

2) structure of table you import to. Please SHOW CREATE TABLE (not DESCRIBE)

3) what it should look like after import (SQL dump)

.. we could look into it. But please keep the test case small – a few rows of data are enough.

But I also never told that you would not need to adjust your workflow or table a little to be able to use our tool!

[Author]

Posts