January 10, 2008 at 11:36 pm
Access DB (external/ remote data source) --> SQL DB
My system is such that I have to constantly import work orders from Access db into SQL and then once proccessed in SQL, I need to update the corresponding records in Access (Export).
I have certain records now in my SQL history table (moved out from my SQL live table during purge).
When I need to import a work order the next time and if this requires data that was imported earlier (and is now in history), I was wondering whether re-importing the exact same record from Access into SQL live table would be all right or should I move from history into live (which I assume is less time consuming as we aren't reading data from an external data source for insert).
Moreover, the next purge would result in duplicate records in the history table if I'm simply importing the same data from Access rather than moving within SQL from History to Live.
Could any one here suggest the best possible solution to this?
January 14, 2008 at 12:57 pm
Let me see if I understand this:
"Access DB (external/ remote data source) --> SQL DB
My system is such that I have to constantly import work orders from Access db into SQL and then once proccessed in SQL, I need to update the corresponding records in Access (Export)."
The first suggestion (if this is possible) is to eliminate the double step. have the SQL table be the back end to the access database. Right now you are entering the data I am assuming via Access then importing into SQL...now this requires a bit of development but it should be possible. If this is not what you had in mind I will continue! 😀
"I have certain records now in my SQL history table (moved out from my SQL live table during purge)."
So the records updated from source Access eventually make their final destination in the History SQL table based on a SQL purge process
"When I need to import a work order the next time and if this requires data that was imported earlier (and is now in history), I was wondering whether re-importing the exact same record from Access into SQL live table would be all right or should I move from history into live (which I assume is less time consuming as we aren't reading data from an external data source for insert)."
The question that pops in my mind is why such the quick purge from live to history if the access db may have more updating to do to the SQL table?
Your question really is 'depends'
Moving your record back from history to live and then trying to do the import may cause error based on your database setup. You may have a constraint or a trigger that prevents duplication, therefore recovering the row from history and then trying to reimport over the existing record may not complete.
Second a trigger/primary/foreign keys on the live and history table may prevent it also from importing again.
"Moreover, the next purge would result in duplicate records in the history table if I'm simply importing the same data from Access rather than moving within SQL from History to Live."
What I would do, and again not giving the entire scenario is that if the record is going to be reimported with either new or correcting data is to truncate the history row and then do the process all over again.
January 14, 2008 at 1:12 pm
What I would do is forget import/export. You should make access a linked server and then you can move huge bulks of data very rapidly.
e.g. Note: You can use this both ways for rapid movement of data.
delete
from access
go
insert into Access
select *
from SQL
Now about inserting the same record that is a no no when it comes to data integrity. If you are going to be moving all the data both ways then I would truncate the data and reload each time, as JSheldon recommended. Otherwise, you risk importing bad data.
January 15, 2008 at 6:55 am
an ideal task for SSIS (or DTS if you're using 2000)
January 15, 2008 at 9:13 pm
Thanks a ton, guys! Yes, I agree with the 'truncate history row' part. That's exactly what I'm going to do.. will have to think over it a little.
Jsheldon, the purge depends on the user. He may or may not sensibly retain data. The system is such that.. data that goes into history may or may not be required later.. there is no real pattern to this. I can't maintain records expecting a work order on it again.. so I let user perform purge.
Adam, I am using a linked server and performing bulk data movement. Could you elaborate on what you were trying to say?
Could you tell me more, Samuel?
January 16, 2008 at 4:38 am
SSIS (SQL Server Integration Services) is an ETL (Extraction, transform, Loading) tool which can take data from a huge number of sources, modify it any way possible and then load it in to a target system.
Possible sources:
Access
Oracle
SQL Server
flat file
Web Sites
Excel
MySQL
XML
DB2
Informix
Basically anything you can get a driver for
The data can the be manipulated, joined to other data, look ups performed, custom scripts ran etc etc etc
Then data can be loaded into a target system (see above list)
SSIS is a whole lot more than that too, I've barely scratched the surface with that explanation 😀
Google it and have a look, there is a lot of information out there about it.
January 16, 2008 at 5:55 am
WOW! Ok, Let me check so I can get back to you!
I have NO experience in database. I'm just a fresher out of college.
So lets see what I can do here.
January 16, 2008 at 6:20 am
I'll warn you now... it does require a fair bit of patience, although extremely powerful it has certain character traits (ie flaws) which you'll need to get used to. Having said that though, when it works its very fast and a beauty to watch, especially when you're dealing with data from disparate systems.
There's a book by Kirk Haslden which I can recommend if you do decide to start learning SSIS
January 16, 2008 at 7:38 am
I will second Samuel's warning about the SSIS learning Curve! Do your self the favor of learning the fundamentals of how to build your dtsx packages properly.
There is just something about watching a dtsx package run, seeing the data flow, the color changes ... a lot like my precious lava lamp!
Samuel, I could not find any books by Kirk Haslden on SSIS. Can you provide an ISBN or title?
Recommended Reading/Study:
Book 1:
Professional SQL Server 2005 Integration Services (Programmer to Programmer) by Brian Knight, Allan Mitchell, Darren Green, and Douglas Hinson (Paperback - Jan 31, 2006)
ISBN-10: 0764584359
ISBN-13: 978-0764584350
--
Book 2:
Expert SQL Server 2005 Integration Services (Programmer to Programmer) by Brian Knight and Erik Veerman (Paperback - May 29, 2007)
ISBN-10: 0470134119
ISBN-13: 978-0470134115
--
SQLServerCental.com
David Bird has posted 2 parts of his 3 part series on SSIS packages. If he keeps to his past release schedule, Part 3 will be posted Monday January 21, 2008. I highly recommend working through his tutorials.
SQL Overview Part 1
http://www.sqlservercentral.com/articles/Integration+Services/61621/
SQL Overview SSIS Package II - Retrieving SQL Error Log
http://www.sqlservercentral.com/articles/Integration+Services/61714/
There is also a Forum Topic just for SSIS, also recommended reading/study.
--
Fantastic place for more SSIS resources.
--
For those folk working with DTS:
--
Happy SSISing
"Key"
MCITP: DBA, MCSE, MCTS: SQL 2005, OCP
January 16, 2008 at 8:02 am
It would probably help if i spelt his name properly
Kirk Haselden
Microsoft SQL Server 2005 Integration Services
ISBN-10: 0672327813
ISBN-13: 978-0672327810
January 16, 2008 at 8:33 am
Samuel,
Thanks for the book reference, I will definitely check it out!
Silly typos!
(I am the typing kign)
*chuckle*
"Key"
MCITP: DBA, MCSE, MCTS: SQL 2005, OCP
January 16, 2008 at 10:02 am
Thanks a ton, guys! Yes, I agree with the 'truncate history row' part. That's exactly what I'm going to do.. will have to think over it a little.
Jsheldon, the purge depends on the user. He may or may not sensibly retain data. The system is such that.. data that goes into history may or may not be required later.. there is no real pattern to this. I can't maintain records expecting a work order on it again.. so I let user perform purge.
Adam, I am using a linked server and performing bulk data movement. Could you elaborate on what you were trying to say?
Could you tell me more, Samuel?
If you are using a linked server, you can directly insert and delete data using an insert statement and the linked server four part name; however, I do not believe this operation qualifies for a minimally logged transaction.
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/a2336a30-1793-4a29-bffc-500e24204d70.htm
Another option aside from learning SSIS is to use the export wizard to copy the data. Make sure to check the delete existing radio button. You can then save the package in SQL server and schedule a job to execute the task.
This may be the best option as you do not have the steep learning curve and the package is completely reusable.
January 16, 2008 at 10:03 am
You would have to make a package for each way SQL to ACCESS and ACCESS to SQL, both of which can be done via the import/export wizard, as I specified above.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply