Fast and effective way of importing 1 million records from excel file into Microsoft SQL Server table

Cindy See 206 Reputation points
2022-09-05T07:38:23.4+00:00

May I know the fast and effective way to import 1 million records from 1 excel file into 1 table?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
14,147 questions
{count} votes

Accepted answer
  1. Olaf Helper 45,291 Reputation points
    2022-09-05T08:53:56.753+00:00

    Or use a SSIS package (it's a ETL tool), which is close to the Import/Export wizard, but more flexible in functionallity.
    https://learn.microsoft.com/en-us/sql/integration-services/sql-server-integration-services?view=sql-server-ver16

    0 comments No comments

3 additional answers

Sort by: Most helpful
  1. CathyJi-MSFT 22,321 Reputation points Microsoft Vendor
    2022-09-05T08:18:28.627+00:00

    Hi @Cindy See ,

    Suggest you using SQL Server Import and Export Wizard. You can refer to below MS document to get more methods.

    Import data from Excel to SQL Server or Azure SQL Database


    If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    0 comments No comments

  2. Cindy See 206 Reputation points
    2022-09-06T02:52:23.583+00:00

    What type of connection manager in SSIS should I use to connect to Microsoft SQL Server 2019?


  3. Cindy See 206 Reputation points
    2022-09-07T02:04:38.007+00:00

    I run a stored procedure of the table using administrator account from Management Studio to openrowset the excel data into the sql table but encountering this error.
    Please advise.
    I have already installed the microsoft access database engine 2010 on the db server.

    The 32-bit OLE DB provider "Microsoft.ACE.OLEDB.12.0" cannot be loaded in-process on a 64-bit SQL Server.


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.