Automated Data import from Access to SQL Server

Results 1 to 2 of 2

Thread: Automated Data import from Access to SQL Server

  1. #1
    Join Date
    Dec 1969

    Default Automated Data import from Access to SQL Server

    The client has an access database from which he is exporting to a flat file. <BR><BR>We wish to upload this file to the webserver, and then import the data into the SQL server via an ASP page. The data needs to create a new record in a table in the SQL and then populate some other tables that use the first record&#039;s identity PK as FKs in those tables.<BR><BR>My strategy is this:<BR>1) Build an ASP page with an upload component to grab the file and pull it up to the database server.<BR>2) Build an ASP page which executes something (a stored procedure? a DTS package?) to populate the first table, and then populate the other tables after the first is populated. <BR><BR>Is this strategy sound? If so, what is the "something"? <BR>If it&#039;s a stored procedure, how do I read from a file in Transact-SQL?<BR>If it&#039;s a DTS package, how can I do the necessary processing to allow the insert of the first record, and then the population of the other fields once that first PK is generated?<BR><BR>Thanks for any thoughts you may have.<BR>Matt P.

  2. #2
    Join Date
    Dec 1969

    Default This is a common question

    If your intent is to automate and schedule this process, then don&#039;t do it thru an ASP page. This is a batch process so instead write yourself a windows script host file(.vbs or .js) or a dos script(i.e. - .cmd or .bat file) to do this.<BR><BR>There are many ways to do it. One way is to use a combination of FTP to transfer the file, the SQL Server BCP command-line utility to do the import and the SQL Server ISQL command-line utility to execute a stored procedure to do your updating/inserting.<BR><BR>Furthermore, if I were you I would create a separate table that mirrors all them fields in your flat file and load into that table. think of it as a "staging" table. Then it is an easy thing to write your insert statements and logic for updating your tables in a stored procedure. Also, you minimize the risk of loading "bad" data directly into your "production" tables. ; )<BR><BR>Once you have your final script you can schedule it to run on your server using windows task scheduler.<BR><BR>Good luck<BR>Pete<BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts