0

I'm trying to create a weather database that is built around my weather equipment.

Right now the weather updates data into an excel spreadsheet once an hour. I've completed the initial import of data from the excel sheet and created a linked server.

What I'm looking to do now is to create an update statement to retrieve the new data from the Excel sheet. I'd like to make it so the update statement is automatically run once a day.

The linked server name is weatherdata and the Excel file name is acuriteweatherdatabase2 with many different column names. I've looked online and tried many of the options but come back with various error messages.

What would be the best way to complete this task?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
bsayles12
  • 1
  • 2

1 Answers1

1

I would approach this as:

  • Schedule a job using SQL Server agent
  • Have the file copied up to the SQL Server or available on a network drive.
  • Load the data into a staging table.
  • Copy the data from the staging table to the final table, using some sort of rowid or date/time stamp to ensure no duplicates.

If the job fails to run for a day, it will be caught up the next day. If you run the job multiple times, it will not insert duplicate data.

I should add that having the database in charge of uploading the data has other advantages. You can change the database structure, without changing the application. You can readily add more data sources, confident that they will not stomp on each other. You can plan the updates around other database maintenance operations. If the source data is not available, you can send out an alert. (I am guessing that source data not being available is more common than the database not being available.)

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thanks for your help. I have SQL server express. How would I create a recurring job without SQL server agent? Thanks. – bsayles12 Aug 19 '17 at 21:23
  • @bsayles12 . . . You would use cron or Windows task manager. Here is one method: https://stackoverflow.com/questions/7201061/how-to-create-jobs-in-sql-server-express-edition. – Gordon Linoff Aug 19 '17 at 21:46