SQL Server Update – Easy Way to Update Excel Data to SQL Server

performancequery-performancesql server

I have a database with some tables "Products, Cores, Clients, ProdCliPrice, etc"

I am developing a software and one of it's functions is to update (or insert) the data in an Excel file to my database.

The Excel file contain a ProductID, a Core Name and a Product Price.

I wrote a code that stores all Core Names and Ids to a array, and then, line by line insert (or update if already exists) the product to the database. Since I have more than 100k rows, it is taking too long.

Could you help me with a better way to insert or update these data?

Best Answer

You coud either use BULK INSERT directly in SQL Server Management Studio like this:

BULK INSERT [DataBase].[dbo].YourTable
FROM 'filepath'
WITH
(
    Firstrow = 1,
    Fieldterminator=';',
    Rowterminator='\n',
    Codepage = 'ACP'
)

For Excel-Files (.xls) you can use this:

SELECT * INTO YourTable FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=YourFilePath', [ExcelSheet$])

You may have to replace the 8.0 with your current office version (8.0 is for Office 2000, 14.0 is for Office 2010, for example).

There's also SqlBulkCopy in C#.

If you don't want to import all columns, I suggest you write a small program, load the file into a DataTable, drop all the columns you don't need and then use SqlBulkCopy

Related Question