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.
