How to Import Data into SQL Server Tables?

The following article describes how to import the data from an MS Excel sheet to a SQL Server table in Microsoft SQL Server Ver. 2016. This method of importing data from source to target looks very simple for experienced developers. But this is much helpful and useful for beginners who have just started their career in SQL Server.

There are many frameworks where anyone will get the master data from a client in an Excel sheet format. The same we need to load into the server. Now, the user might be thinking about how we could import data into an SQL table from Excel without writing a single query line to the database. Learn Sql database administrator training for more skills and techniques. The following article explains how to use SQL Server’s inbuilt function of import and export wizard and load data in the table.

SQL SMS provides the Import Wizard task that users might use to copy data from one source to another. Moreover, users can choose from different source and target data source types. They can select tables to copy or specify their own query to collect data and save the work as an SSIS package.

To start we should launch SSMS by clicking SQL Server Management Studio from the MS SQL Server program group. Upon launching SSMS users will be induced for a connection; connect to the Database Engine.

Inserting data from excel to SQL table

In the following context, we will try to learn how to insert data from the spreadsheet to the SQL table.

Transferring data from MS Excel to SQL Server is in progress task in the business world that reckons on Microsoft’s SQL-Server databases. There are numerous tools and techniques available to complete this task, each with different advantages. And can also complete with multiple levels of complexity.

I will try to brief a few of the different tools, as well as their advantages and use cases. The most common tools available to import an Excel file to a table in SQL Server are the following;

SQL Server Import Wizard –

It’s an importing tool based on wizards within SQL Server Management Studio (SSMS). It can be useful for one-time imports when users have an Excel document with data that he needs to import into an SQL table. The benefits include flexibility and lots of configurations to fine-tune the import task. The biggest drawback is that you need to run through a dozen Wizard dialogs with lots of settings each time you need to import the data. More info about the Import Wizard is available here.

SSIS –

This is the transport medium for transferring data between multiple sources. Users can do almost any task he likes but he may need to put in a lot of time to get started. Then it will take still more time to manage and modify the solution down the line.

The BCP utility –

This is a command line-based tool that offers a large number of settings. If the user is a coder, this is the best useful tool.

The above tools are great to move the data. Especially, if the user has the demand to import data from an Excel file to SQL table on a daily or weekly basis, these are useful. The downside is that the tools require numerous steps to set up and will take a lot of time to configure.

SQL Server import data from excel

The following steps will explain the import of data from the excel sheet. Here, I have built a sample data using an Excel sheet that includes employee details.

Let’s look at the steps below. Here we have one Excel sheet with employee details data with six columns as above. This data we will use as an example and insert it into the employee table.

Step I

At first, we have to run and search for SQL table import and export data.

Step II

Then click on the import and export wizard and click on the “Next”.

Step III

Once the user clicks on “next” he will get an option to select a data source from where he wants to load data. In the below screen, select the data source as the MS Excel from the drop-down list and click on the “Next”.

Step IV

Here, the user has to select an Excel sheet from the folder by using a browse option where the Excel sheet is with Employee data.

Step V

No user has to select the target from the below drop-down list. Here we have selected the SQL server as a target as we want to import data within the SQL server.

Step VI

Later, the user needs to click on the “Next” button and select the server and database name.

Go to the next screen below;

Step VII

Here the user has two different options to import data; the first one, he can copy directly to the table. The second one is to write a query and load it into the table. We have chosen the first option here.

Step VIII

Now, we have to click on the “Edit mapping” option to set our mapping like data type, invalidate, and size for a table column. SQL server dba online course helps you to learn more effectively.

Step IX

Then we need to click on the “Next” option and we are ready with a successful message for our Excel sheet to insert the employee table.

Finally, we have inserted employee data to table Employee of Employee Database.

Copy & paste data — Excel to SQL Server Views

The displayed copy and paste method to insert data from Excel to Server also work great when the user needs to insert data into a SQL Server View, as long as the View only includes data from a single table.

Under View in SQL Server that includes data from various mixed tables, users cannot insert new rows. But he can update the data as he only updates columns that arise from the same base table. only includes data from a single table.

In case the data that user copies from his Excel document do not match the data types of the columns in the SQL Server table, the insertion of the data will be canceled and the user will get a warning message. This will happen for every row that user paste from Excel document to SQL Server. If the user pastes 500 rows from Excel with the wrong number of columns, he will get one warning message for every row.

To avoid this issue, the trick is to begin to copy only a single row of data and paste the same into the SQL Server table.

Requirements & limitations

To copy-paste data from Excel to Server, one needs to install MS Excel and SQL Server Management Studio.

The copying of data from Excel to SQL Server works greatly up to a few thousand rows of data with rational load times. It might be a simple process.

The copy and paste method is best useful for both tables with business keys and tables with auto-increasing ID keys.

Summing Up

Here, in the above article, we have gone through various steps and techniques that are useful in importing data into SQL table. Mostly, the copy-paste method in this regard is the great one. While copying data, one should take necessary measures that may help in easy work. The copy-paste method also works great for the tables with an auto-enhancing identity key. Here, one should remember to add an extra blank column left of the data columns as mentioned above. To get more details on this, go through the SQL Server Online Training.