Importing SQL server table data to the Excel

 Sometimes we are working on the dev environment and inserting data into some table like product details, after that the same data we need to insert into the other environment like QA and prod.

The table may have more the 500 rows in this case it is difficult to creating an insert script to migrate the data.

Simplest ways to Import the data from the table in excel and after that we will export the data from excel to SQL server table.

 Let’s see how to Importing the Sql Server table data to Excel.

Right-click on the database àselect tasksà Export Data

   

Select the server name and the database name  

If we are using the SQL Server Authentication we need to provide the user name and password.

Click on the next

 

Select the destination. Here we are selecting the Microsoft Excel . Selecting the path where we want to store this excel file and also we need to select the version of the excel.

IF we make Check to the First Row has column names then excel files have the column name otherwise it doesn’t have the column name.

Click next    

Here we can set the option that we are going to export the data. If we want to export the multiple table or view then we need to select the below option

                Copy data from one or more table or views

If we want to export some columns from a table in this case we need to provide the query. Need to select the below option and write the query

                Write a query to specify the data to transfer

See below  

Click on the parse to check our query is correct or not. We correct us will get the message like below

                           
Else we will get below if any error occurs  

We have made some error in the query so we are getting the above error.

Here we are moving the table data so we are going to the first option.

Clicking next   

Here we need to select the source table which we want to export the data into excel.

If we want to edit the mapping we can do it. Click on the Edit Mappings button

 

Basically, in this screen, we can make the change on the data type of the fields. 

If we want to drop and re-create a destination table if exist then we need to make this box as checked.

After doing changes in this screen click ok.

Now click on next  

Here we can review the mappings.

  

Here we can set if any error occurs in this case what we want to do either fail or ignore.

Same case on the Truncation.

Now click on the next. 

Click on the next.    

Here we are getting the summary.

Now we can click on the Finish to export the data.

  

Data Exported successfully.

Click on the Close.

Now data have been exported to the excel file

See the file.

         

In this way, we can export the data from sql server database table.

Next post we will see how to import these data into the sql server table.