Append data automatically after every 2 hrs

1

I need to automate the procedure to automatically append data into the tables after every 2 hrs from an excel file.

Is it possible using Store Procedure.

2

there are a few options

  1. powershell
  2. SSIS
  3. OpenRowSet

https://docs.microsoft.com/en-us/sql/relational-databases/import-export/import-data-from-excel-to-sql?view=sql-server-2017

Which are you more comfortable with

3

I'm asked to create a store procedure that would automatically update the tables from excel sheet every 2 hrs. Can it be done using OpenRowsheet.

4

yes it can be done using OPENROWSET

insert into Staging
SELECT * 
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0; Database=C:\FTPdrop\Data.xlsx', [Sheet1$]);

C:\FTPdrop is on the server.

Needed to install this
https://www.microsoft.com/en-us/download/details.aspx?id=13255

then using SQL Server Job you schedule it to run every hour and make the call to the stored procedure.