| Author |
Topic |
|
sccrsurfer
Starting Member
43 Posts |
 Posted - 2013-01-29 : 10:43:44
|
Hi guys, currently we're using Access and SQL. While it would be ideal to eliminate the use of Access, it's not an option right now but will be in the future. Users do not update existing records, they only add new ones into Access on a daily basis. I need help forming a package that will import those new records into SQL every day. Any suggestions? Thanks! |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-29 : 10:46:24
|
| you can use export import wizard to create a data export package. Then create a sql server agent job to call and execute this package daily as per your required schedule------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
sccrsurfer
Starting Member
43 Posts |
Posted - 2013-01-29 : 11:13:35
|
| Yes, but how will SQL know which records are new records in Access? Will there be an obvious way to set this identifier in the SQL export/import wizard or SQL agent? I have a date field in Access that can be used, though the datetime in the SQL 2005 (the version I have) makes things a bit confusing. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-29 : 11:35:19
|
| do you've timestamp column or id column in access?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
sccrsurfer
Starting Member
43 Posts |
Posted - 2013-01-29 : 13:15:03
|
| How do I design it to import only the new records? Will that be an option in the wizard? |
|
|
|
sccrsurfer
Starting Member
43 Posts |
Posted - 2013-01-29 : 13:36:51
|
| Sorry for the double reply :) I am very new to this and found my SQL simulation on SQL zoo to be very different than the real thing. I do have a date field in Access with data type set as date (in Access). I would want to have the package run every day to pull in TODAY. However, not sure how to do that in SQL 2005 where there is datetime. |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-01-29 : 13:58:56
|
I would think that you want to filter the data while pulling the data, rather than when inserting the data into the SQL table. If you do want to filter it at the SQL end, the query you use should have a WHERE clause like this:WHERE YourDateColumn >= DATEADD(d,DATEDIFF(d,0,GETDATE()),0) The part to the right of >= is removing the time portion from the current timestamp. I am not well-versed with Access, but if you want to do the filtering in Access, you should be able to use something like DATEVALUE(NOW()) in a similar manner to pick up only the date portion to compare. |
|
|
|
sccrsurfer
Starting Member
43 Posts |
Posted - 2013-01-29 : 16:53:50
|
| Thank you all for contributing. James K, your query really helped me filter what is already in SQL. I've been playing with SSIS in business intelligence studio all day and have come to understand my own requirements and can communicate them in better terms. I will post a new topic (with a new request) to clarify. Thanks again everyone. |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-01-29 : 17:04:33
|
| You are very welcome - glad to be of help. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-29 : 23:30:33
|
quote:
Originally posted by sccrsurfer
Thank you all for contributing. James K, your query really helped me filter what is already in SQL. I've been playing with SSIS in business intelligence studio all day and have come to understand my own requirements and can communicate them in better terms. I will post a new topic (with a new request) to clarify. Thanks again everyone.
see the logic behind date filterhttp://visakhm.blogspot.in/2012/07/generate-datetime-values-from-integers.html------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
Import new records from Access to SQL daily