How to import Excel data in SQL Server 2014

If you have data in excel file and you want to import in sql table, then this article is going to explain everything.

There are two ways first if you want to import all excel file with header names in new table and other if you want to append data in existing table data without header names.

First let’s import all data in new table:

Here is my test data Excel file.

 

Now start SQL Server Management Studio and connect database engine and expand the database where you want to import the data.

Now follow wizard steps carefully.

Now select Microsoft Excel from Data Source drop down and click next.

Browse the path for excel file and select Excel version and click Next.

Now select Microsoft OLE DB Provider for SQL Server and click Next.

Enter sql server authentication detail and select database where you want to import data and click next.

Check first option copy data from one or more tables or views and clikc Next.

Here comes the important part, as you can see the default names for source and destination, If you want to create a new table with default name then no need to change anything and click next.

If you want to append data in existing table, then expand the Destination and you can see all tables and select the table where you want to import the data.

Note – if you want to append the data in existing table then make sure your excel file columns and table columns match and if sql table has primary key then if excel file that column should be blank.

If you want to change mapping, then click on Edit mappings like drop and recreate destination table or enable identity insert etc.

Check Run immediately and click Next.

Now finally click Finish.

Final wizard show the all output like how many rows transferred and success status. Click close to close the window.

Now open you sql server and select data then you can see there is one more table is added, you can check all columns and data is available there.

Conclusion

In this article, we have seen how to import data from Excel to SQL Server. If you have questions or comments, drop me a line in comments section.