ODBC Import – Child Table

[rbma]

Currently you can filter records based on the fields within the table. Will it be possible to filter records based on fields from another table (parent table)? Thanks.

[peterlaursen]

I shall not attempt to answer the question! My SQL knowledge is not sufficient!

But if there is a WHERE statement that is legal with the source database that allows for it, and if it is implemented in the ODBC driver then: YES, if not: NO!

[peterlaursen]

One posibility is sub-queries

In MySQL 4.1 and above thi is legal SQL

select * from childtable where childcolumnname = (select whatyouwant from parenttable where parentcolumnname = whereyouwant);

in the SQL-WHERE-window of SQLyog you enter then

Quote:

childcolumnname = (select whatyouwant from parent where parentcolumnname = whereyouwant)

I don't know the exact syntax for subqueries with other DB-servers.

Also I don't know if your data are structured so that you can use it!

And besides tables don't need to be related to use subqueries.

Wait for some SQL expert to show up!

[rbma]

I am not at work to test your suggestion. This is what I want to achieve with the following tables during the ODBC Import:

NAME

id

name

member_type

paid_thru

CORP_PROFILE

id

comp_desc

In NAME, import only member_type is Corporate and paid_thru is 12/31/2005.

In CORP_PROFILE, only import records that have records in NAME linked by id.

Can the Scheduled ODBC Import do this?

Thanks.

[peterlaursen]

Can you describe the relations between the tables ?

Is there a relationsship between id in NAME and id in CORP_PROFILE?

(that' how I understand it!) or is the relationsship between other columns in the two tables ? or is the not a relationship defined with the datastructure at all (in the meaning of a Foreign Key), but nevertheless a correspondance in data ?

If you can write an ordinary SQL (using the syntax of the source D:cool: like

SELECT * FROM table_name WHERE {wherecondition] that works

then the ODBC-tool also can! simply put the {wherecondition] in the SQL_WHERE.

Quote:

In NAME, import only member_type is Corporate and paid_thru is 12/31/2005.

This is a quite ordinary SQL, so this is no problem!

Quote:

In CORP_PROFILE, only import records that have records in NAME linked by id.

this is still quite unclear to me what you mean here!

There are some rows in CORP_PROFILE that are not referenced by (or does not correspond to) any row in NAME. And those rows in CORP_PROFILE that are not referenced (or where there is no correspondance) shall not be imported.

Is that how to understand it ?

[rbma]

These are in MSSQL:

NAME:

1,Name1,Corporate,12/31/2005

2,Name2,Corporate,12/31/2005

3,Name3,Corporate,12/31/2004

4,Name4,Active,12/31/2005

CORP_PROFILE

1,Desc1

2,Desc2

3,Desc3

4,NULL

They are related by ID

The resultant tables should be after the Scheduled ODBC Import:

NAME:

1,Name1,Corporate,12/31/2005

2,Name2,Corporate,12/31/2005

CORP_PROFILE

1,Desc1

2,Desc2

Thank you.

[peterlaursen]

Thanks .. I think it is clear now!

this SQL_WHERE

Quote:

type ='corporate' and paid_thru = '2005-12-31'

will easily handle table 'name' (remember to use exact date-syntax as demanded by MS SQL server)

But the other one cannot be done with ODBC-import, I believe.

No SQL_WHERE is possible (or I am just not clever enough!)

But I have another solution. Just import everything (no SQL_WHERE) from table 'corp_profile'. And after import then delete the rows that should not be there with this SQL

Quote:

use db_name;

create table corp_profile_cleanup select corp_profile.id, corp_profile.corp_desc from corp_profile,name where type ='corporate' and paid_thru = '2005-12-31' and corp_profile.id = name.id;

drop table corp_profile;

rename table corp_profile_cleanup to corp_profile;

You can do that with “Notification Service” and the and can be executed from one bat-file, and that bat-file can be scheduled with the windows scheduler as I have described it in my WhitePaper here http://www.webyog.com/whitepaper/Using_SJA_2006_07_18.pdf (look for paragraph named “Combining Things”.

But you should NOT import the FOREIGN KEY here (!) as it might not let you drop table 'corp_profile' . When I say “might not” that depends on whether it is a one-one, one-many or many-one relationship! Best is not to import FK's here at all.

Create it (or them if you want a one-to-one relationsship) afterwards – manually or with a concluding ALTER TABLE statement in the jobfile, like

Quote:

alter table  `name` add foreign key (id) references `corp_profile` (id)

(or the other way or both!)

Attached image should document that it works!

[rbma]

peterlaursen wrote on Jul 31 2005, 08:50 PM:

But I have another solution. Just import everything (no SQL_WHERE) from table 'corp_profile'.  And after import then delete the rows that should not be there with this SQL

🙁 I came to the same conclusion. I was hoping not to have to do this. There is another table linked to NAME with 15,000 records that will have to be downloaded. Hopefully the Scheduled ODBC Import will be enhanced to accomodate this situation.

Again, thank you for all your help.

[peterlaursen]

Quote:

Hopefully the Scheduled ODBC Import will be enhanced to accomodate this situation

give the boy a lollipop and he'll want a candy shop 😀

You have far more possiblities with SQLyog import tool than any other import tool!

BTW: can't you avouid those redundant rows by using “ON CASCADE DELETE” ?

If you need those rows for some reason you might also consider copying the DB on the SQLserver and to delete the unwnated rows there with the copy!

[rbma]

peterlaursen wrote on Jul 31 2005, 10:54 PM:

give the boy a lollipop and he'll want a candy shop  😀

😛

peterlaursen wrote on Jul 31 2005, 10:54 PM:

You have far more possiblities with SQLyog import tool than any other import tool!

Agree 100%. Thank you Webyog!!!!

peterlaursen wrote on Jul 31 2005, 10:54 PM:

BTW: can't you avouid those redundant rows by using “ON CASCADE DELETE” ?

Unfortunately, it is a custom application in which we have no control on how it operates.

peterlaursen wrote on Jul 31 2005, 10:54 PM:

If you need those rows for some reason you might also consider copying the DB on the SQLserver and to delete the unwnated rows there with the copy!

Yes but we are attempting to automate the whole process of importing records from the internal system and then synchronizing the local copy to the hosting company without manual internvention.

[rbma]

peterlaursen wrote on Jul 31 2005, 08:50 PM:

use db_name;

create table corp_profile_cleanup select corp_profile.id, corp_profile.corp_desc from corp_profile,name where type ='corporate' and paid_thru = '2005-12-31' and corp_profile.id = name.id;

drop table corp_profile;

rename table corp_profile_cleanup to corp_profile;

This does not carry over the primary key info to the new table. How to modify this code to do this? Thank you.

[Ritesh]

Quote:

create table corp_profile_cleanup select corp_profile.id, corp_profile.corp_desc from corp_profile,name where type ='corporate' and paid_thru = '2005-12-31' and corp_profile.id = name.id;

This statement will not create index. You have to create it explicitly! You can do it using SQLyog's Index Manager 🙂

[peterlaursen]

if the proces proces shall be completely automated you can add an ALTER TABLE adding an index at the end of the sequence. To get the exact syntax do it manually once and copy SQL from HISTORY-pane.

it is something like

ALTER TABLE `mydb`.`mytable` add index `indexname` ( `column(s)` )

[Author]

Posts