In this article we will explain how to import excel data in sql server database.
Sometimes developer/programmer need to insert records in sql server database from excel sheet.
If you have data in excel file and you want to import in sql table, then follow steps to explain everything.
Step-1: Now start SQL Server Management Studio and connect database engine and expand the database and Right click on database where you want to import the data.
Step-2: Now Click to Next
Step-3: Now select Microsoft Excel from Data Source drop down and Browse the path for excel file and select Excel version and click Next.
Step-4: Now select Microsoft OLE DB Provider for SQL Server and Enter sql server authentication detail and select database where you want to import data and click next.
Step-5: Check first option copy data from one or more tables or views and clikc Next.
Step-6: 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. 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 and click next.
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.
Step-7: If you want to change mapping, then click on Edit mappings like drop and recreate destination table or enable identity insert etc.
Step-8: Check Run immediately and click Next.
Step-9: Now finally click Finish.
Step-10: Final wizard shows all output like how many rows transferred and success status. Click close to close the window.
Step-11: Now open you sql server and select data then you can see there is added all records.
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 the comments section.