1

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?

2

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

  • will that work for Excel? This looks a .csv import. – Max Vernon Mar 7 '17 at 21:29
  • This really looks like a .csv import; My Excel file have headers and if it's possible I don't want to import all columns – G. Knoxx Mar 7 '17 at 21:48
  • 1
    @G.Knoxx I updated the answer with an import for excel files. But it sounds like you're better off writing a small program in C#. – waka Mar 7 '17 at 22:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.