How to import an Excel file into SQL Server?

Question

I have the data in an Excel sheet - actually it's in xlsx format, since it is now 2020. My requirement is to get this data into the SQL Server as following:

1. I want it ad hoc, along with the use case being fed to tables with the test data, or as not so frequent data loads of small amount (e.g., less than 3k rows)

2. I also want it in a robust, repeatable, and possibly in an automated way for a production system.

Answer 1

1.Open SQL Server Management Studio and hook up with your SQL Server database.

2.Expand the Databases and therefore the Tables folders for the table where you'd wish to insert your data from Excel.

3.Right-click the table and choose the fourth option – Edit Top 200 Rows.

4.The data are going to be loaded and you'll see the primary 200 rows of knowledge within the table.

5.Switch to Excel and choose the rows and columns to insert from Excel to SQL Server. Right-click the chosen cells and choose Copy.

6.Switch back to SQL Server Management Studio and scroll right down to the last row at rock bottom and locate the row with a star within the left-most column.

7.Right-click the star within the column header and choose Paste.

8.Your data from the Excel is now pasted into your table in the SQL Server

If you want to learn more about SQL, Check out this SQL training online by Intellipaat.