CODEWITHSUNDEEP
sqlsqliteautomation
Will
Will

Reputation: 153

How to automatically update a SQL database

So I'm working with a professor who wants me to create a SQL Database containing information from csv files (from the New York City Department of Transportation). I've written program that takes the csv file and converts it into the appropriate sql commands. So my question is, how do i automate the database so that every 5 minutes or so a program downloads the new csv file, runs it through my csv-to-SQL command program, and then enters the output from my csv-to-SQL command program into terminal (what I use to interface with my SQL database)? Is there a specific language I should look into, I've seen people talk about cron?

Upvotes: 1

Views: 1748

Answers (3)

Jason Chrin
Jason Chrin

Reputation: 1

What you can do is use Sql Server Integration Services (SSIS). It's basically a workflow package built into Sql that will let you handle tasks like this. You should be able to import the CSV into a temp table and then run your appropriate queries against it.

This is Azure specific but working against hosted SQL should be similar. https://learn.microsoft.com/en-us/sql/integration-services/import-export-data/import-data-from-excel-to-sql

Upvotes: 0

James
James

Reputation: 698

The simplest thing you could do is loop your script.

If you're running PHP you could do something like:

$running = true;
while ($running)
{
    // Your code that gets and converts CSV
    // and then saves it in SQL.

    $running = $some_error ? false : true;
    sleep(5000);
}

I don't know what you're using but mind the logic, not the language.

Upvotes: 0

pbickford
pbickford

Reputation: 91

cron is a reference to making a scheduled task under Unix; the Windows equivalent is to set up a task to run using a Task Scheduler.

I don't know that there's a pure SQL answer to your problem--probably the way I'd approach it is by writing a simple Import program in the language of your choice, compiling it down to an .EXE, then setting up a Task Scheduler command to run the program every 5 minutes. (Alternately, you could leave the app up all the time and simply let a timer execute every 5 minutes to trigger the import).

Upvotes: 2

Related Questions