MySQL importing data

[VinylxScratches]

Is this possible to automate?

This will be a daily process. To import these names into a database. The problem is that it will be constantly expanding and stuff being removed...

1st time....
#List1
#List2
#List3
#List4

2nd time
#List1
#List2
#List3
#List5

So I would like it to consider that #List 4 is gone and to delete it and add #List5.

The other consideration is that there will be an End Date for each list so I'd like to keep that alive somehow... I'm not exactly sure how to do it yet....

Basically, I want to create a system where if a user requests a distribution list in AD, it's extracted out from AD and imported a MySQL database and an end date is tagged on so we can maintan dlists.

I'm trying to manually do the import into MySQL now. I know how to get data out of AD...

 

[VinylxScratches]

Ok.

SO I have a table called tbl_dlists that has this

primary key id that increments
dlistName = distribution list name
endDate = date parameter

I am able to load an INFILE into the MySQL database, my problem is this.

If my database currently has

1, #List1, 05/26/2011
2, #List2, 05/27/2011
3, #List3, 05/28,2010
4, #List4, 05/29/2012

and I need to load
,#List1,
,#List2,
,#List4,
,#List5,

I need it to disregard 1, 2, and 4, delete 3, and add 5. Is this possible to do with just MySQL or do I have to use some other means?

 

[GeekDrew]

I can't think of any way to do that without running additional statements and using an "import table". Along the lines of...

-- human readable copy of table with existing data
tbl_dlists
key,dlistName,endDate
1, #List1, 05/26/2011
2, #List2, 05/27/2011
3, #List3, 05/28,2010
4, #List4, 05/29/2012

-- human readable copy of import table with data from load infile
tbl_dlists_import
dlistName
#List1
#List2
#List4
#List5

-- deletes #List3 from tbl_dlists because it doesn't exist in tbl_dlists_import
DELETE
FROM tbl_dlists
WHERE dlistName NOT IN (
SELECT dlistName FROM tbl_dlists_import
);

-- deletes #List1, #List2, #list4 from tbl_dlists_import (effectively disregarding them)
DELETE
FROM tbl_dlists_import
WHERE dlistName NOT IN (
SELECT dlistName FROM tbl_dlists
);

-- inserts #List5 into tbl_dlists from tbl_dlists_import
INSERT INTO TABLE tbl_dlists (dlistName)
SELECT dlistName
FROM tbl_dlists_import;

-- empties tbl_dlists_import
TRUNCATE TABLE tbl_dlists_import;

My syntax may not be completely correct for MySQL, but that's the general idea that just came to mind. There are almost certainly better/faster ways to do this, but this was the fastest method for me to type, that came to mind.

 

[VinylxScratches]

Thank you, that really helps!

 

[VinylxScratches]

Alright, so I figured out the Delete statement on things different but now I'm having a hard time figuring out the insert new dlists function which obviously needs to be done first so then it can delete the difference once data is updated...

This command does a SELECT statement that displays WHAT NEEDS TO BE IMPORTED... I can't seem to grasp how to do that part on how to convert this to import data .

SELECT * FROM tbl_dlists_import
LEFT JOIN tbl_dlists
ON (tbl_dlists.dlistName = tbl_dlists_import.dlistName)
WHERE tbl_dlists.dlistName is NULL;

 

[GeekDrew]

Alright, so I figured out the Delete statement on things different but now I'm having a hard time figuring out the insert new dlists function which obviously needs to be done first so then it can delete the difference once data is updated...

No, at least according to the requirements you previously provided, you do not need to import the new rows first. If you're using the queries that I provided previously, you'd insert the new rows as the third step.

This statement deletes rows from tbl_dlists if they don't exist in tbl_dlists_import. This meets your requirement that rows that are not in the import file are removed from tbl_dlists.

DELETE
FROM tbl_dlists
WHERE dlistName NOT IN (
SELECT dlistName FROM tbl_dlists_import
);

This statement deletes rows from tbl_dlists_import if they DO exist in tbl_dlists. This step is used so that you can determine which rows in the import table do not exist in the main table (because those are the only rows that will be remaining in the import table. Note that I just caught a typo: in my original post, I used "NOT IN"; I should have used "IN".

DELETE
FROM tbl_dlists_import
WHERE dlistName [B]IN[/B] (
SELECT dlistName FROM tbl_dlists
);

This statement then inserts rows into tbl_dlists if they exist in tbl_dlists_import and did not previously exist in tbl_dlists (it does this because we've deleted all of the rows from the import file *except* for the new rows)

INSERT INTO TABLE tbl_dlists (dlistName)
SELECT dlistName
FROM tbl_dlists_import;

And finally, this statement completely empties tbl_dlists_import, so that it's ready to use the next time you want to import data.

TRUNCATE TABLE tbl_dlists_import;

Note that this is mostly equivalent to the below statement, which tells the database engine to delete all rows from tbl_dlists_import.

DELETE FROM tbl_dlists_import;

 

[VinylxScratches]

Is that an extra step to, delete from the import table?

Why not find what's not in tbl_dlists and import it... then compare between tbl_dlists and tbl_dlists_import to see what has gone from the import table and delete it from tbl_dlists?

 

[GeekDrew]

Is that an extra step to, delete from the import table?

Why not find what's not in tbl_dlists and import it... then compare between tbl_dlists and tbl_dlists_import to see what has gone from the import table and delete it from tbl_dlists?

No, the delete from table is the same as truncate table. Was just trying to explain it to you.

You could do it that way too, that's just not the way that first occurred to me (for whatever reason, that's the way my mind works).

In that case, I'd go with something like these queries:

#1 Inserts into tbl_dlists if it exists in the import but not main

INSERT INTO TABLE tbl_dlists (dlistName)
SELECT dlistName
FROM tbl_dlists_import
WHERE dlistName NOT IN ( SELECT dlistName FROM tbl_dlists);

# Deletes from tbl_dlists if it does not exist in import

DELETE FROM tbl_dlists WHERE dlistName NOT IN (SELECT dlistName from tbl_dlists_import);

# Empties tbl_dlists_import

DELETE FROM tbl_dlists_import