SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Import new records from Access to SQL daily
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sccrsurfer
Starting Member

USA
43 Posts

Posted - 01/29/2013 :  10:43:44  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 01/29/2013 :  10:46:24  Show Profile  Reply with Quote
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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

sccrsurfer
Starting Member

USA
43 Posts

Posted - 01/29/2013 :  11:13:35  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 01/29/2013 :  11:35:19  Show Profile  Reply with Quote
do you've timestamp column or id column in access?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

sccrsurfer
Starting Member

USA
43 Posts

Posted - 01/29/2013 :  13:15:03  Show Profile  Reply with Quote
How do I design it to import only the new records? Will that be an option in the wizard?
Go to Top of Page

sccrsurfer
Starting Member

USA
43 Posts

Posted - 01/29/2013 :  13:36:51  Show Profile  Reply with Quote
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.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3744 Posts

Posted - 01/29/2013 :  13:58:56  Show Profile  Reply with Quote
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.
Go to Top of Page

sccrsurfer
Starting Member

USA
43 Posts

Posted - 01/29/2013 :  16:53:50  Show Profile  Reply with Quote
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.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3744 Posts

Posted - 01/29/2013 :  17:04:33  Show Profile  Reply with Quote
You are very welcome - glad to be of help.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 01/29/2013 :  23:30:33  Show Profile  Reply with Quote
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 filter

http://visakhm.blogspot.in/2012/07/generate-datetime-values-from-integers.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000