using bulk insert to automatically populate sql table, then export to xml

Question

Hi there,

I'm doing a project at the moment which involves using bulk insert to fill an sql table with weather data.

BULK INSERT TableWeather FROM 'C:\Program Files\EasyWeather\EasyWeather.dat' WITH (FIELDTERMINATOR = ',')

This seems to work fine but I need to do this every fifteen minutes and also actually overwrite the data from the last time so that the table size doesn't get out of control. I've been checking everywhere but Im not so handy with sql code. Do I need to create a stored procedure and automate that?

I also need to do it with a view to exporting the new data as an xml every fifteen minutes as well, which will be used by an swf for display on the asp.net website.

Any advice'd much appreciated, Thanks

Answer 1

Are you using SQL Server? If so you can use SSIS package to do this.

Help on SSIS --> link text

Answer 2

you can setup a Sql job or windws schedule job to run every 15 mins.. add a truncate statement before you do bulk insert.

Answer 3

SQL Server still has the crusty old bcp utility, and the handy (relatively new) sqlcmd utility. Not sure if they're available for your version, but if they are, I would recommend it. You just put the bcp and sqlcmd statements in a Windows batch file and run them.

Something like this should work (you'll need to modify the switches on the commands)...

sqlcmd.exe -SMyServer -dMyDatabase -b -Q "delete from TableWeather"
bcp.exe MyDatabase.dbo.TableWeather in C:\Program Files\EasyWeather\EasyWeather.dat -SMyServer -T

See this link for more on bcp and this one for sqlcmd.

This may help you with your XML, too. Use sqlcmd to execute a stored proc that formats the data into XML and stores it in a table, and then use bcp to export it (bcp goes both ways.)

Hope this helps.