I am looking for the most optimal solution on how to import my excel file into the SQL Server Database as a table on an ongoing basis (i.e. daily).My excel file is relatively large, about 100 columns across and currently about 8000 rows long. The column size will remain fixed but the number of rows will increase each day by about an extra 50-200 rows.I thought a stored procedure tied to SQL Server Agent Jobs would do the trick but then decided to use something more simple like the code below and just append the table as need be. I also read into SSIS packages but thought maybe that was overkill for what I need to do.
SELECT * INTO XLImport3 FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data Source=C:\test\xltest.xls;Extended Properties=Excel 8.0')...[Customers$]
I'm still very much new to SQL Server but would appreciate any advice/feedback?
Maybe the easiest way to go about this would be to generate an SSIS package by using the Import Data option in SSMS. You'll find the option by right clicking the database in SQL Management Studio, open Tasks submenu and at about the bottom you'll find the Import Data. It will open a wizard to import data which basically generates an SSIS which you can save for later use and schedule using an SQL Agent Job.
If you haven't created the table to import into, the wizard will also enable you the generate it based on your Excel file. If you need the generate the table, I suggest you to walk through the wizard, don't save the ssis package, instead run directly.
Once you've the table generated, generate the import SSIS using the same wizard. This time around you'll be able to select the delete all rows in destination option.