Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Will H
Yak Posting Veteran
56 Posts |
 Posted - 2008-02-29 : 14:24:34
|
| I've got an access table with about 2 million rows. I'm using this to update a table in SQL that holds pretty much the exact same data, only with an added Identity column. From week to week, the access table grows. For example, next week it may have 2.1 millions rows, the week after 2.2 million, etc. The goal of the DTS is to keep the SQL table up to date w/ the access one. In the past, this has been done by deleting everything from the SQL Table and then importing the ENTIRE access table. This not only takes more time then need be, since the majority of the records *already* existed, but it also threw referential integrity out the door - other tables should be referencing the Identity in the SQL Table. IDEALLY, the only rows that would be transferred from the access file are ones that don't already exist in the SQL table. I don't want to re-invent the wheel, and have to confess being a little under-schooled on all that SSIS has to offer. Is there a Data Flow Transformation that would solve this?? Any other advice? If all else fails, I'd probably just dump the entire access table to a temp table and then insert vals into the production table that don't exist, but even this would require more temp hard drive space then I'd like. Thanks! |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-03-01 : 02:50:37
|
Use the LOOKUP thingy. The green link is for those matching, the red link is for those not matching.
E 12°55'05.25"N 56°04'39.16" |
 |
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2008-03-01 : 11:22:37
|
That access database is going to run out of room pretty soon....
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
Will H
Yak Posting Veteran
56 Posts |
Posted - 2008-03-03 : 11:51:30
|
| Peso - Brilliant!! Thats exactly the sort of simple thing that I feel ignorant for not knowing. Dataguru - Access sucks, I agree. The folks upstream actually split the .mdb across 3-4 files right now to be able to fit other information and not pass the size limit. The whole thing is stupid, stupid, stupid but I've been assured there is *NO* way to get the info any other way. |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2008-03-03 : 19:45:17
|
it has to get into access somehow...find out where and prove them wrong. I did it at my job..they had two product groups being dumped into two sets of 5 Access db's each...none of which ever worked right and did production reporting off of the access databases..they had to periodically purge data into an archive just keep them in check. absolutely absurd. I found out where the mainframe files came from, built SQL databases for them, ripped apart all the code in the Access report macros they set up and converted them to sprocs. Result was elimination of 4-5 full time jobs, and time improvement on reporting from Access to 5 minutes instead of 2 weeks.People who say there is no way are just uninformed or too set in their ways to be willing to change.
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
|
|
|
|
|
Insert vals into SQL from access that don't exist