How to insert Excel or CSV data into Table by using Graphical User Interface in SQL Server - SQL Server / TSQL Tutorial Part 102

Scenario:

You are working as SQL Server developer, you got an excel file or CSV file that contains data for dbo.Customer table. How can you insert Excel or CSV data to table by using Graphical user Interface.

Solution:

Step 1: 
Let's create dbo.Customer table first by using below script.

USE yourDatabaseName
Go
Create Table dbo.Customer(
Id int identity(1,1),
FName VARCHAR(50),
LName VARCHAR(50),
Age int,
DOB Date,
Salary Numeric(6,2))




Step 2:
Go to database , then tables and then right click on dbo.Customer and choose Edit Top X rows as per your settings.

How to insert Excel or CSV data to Table by using Graphical User Interface

Below windows will open that will let you insert the records into table dbo.Customer. 

How to insert records into table from Excel by using Graphical interface in SSMS

Step 3: 
Open the Excel or CSV file and copy the data as shown below. Don't copy the header as we don't want to insert header values into table.
Insert Excel data into SQL Server Table by using Graphical User Interface

Step 4:
Noticed that the columns and copied data has the same order. Come to SSMS and then paste the data.
How to insert Excel or CSV data into SQL Server Table Manually by using SSMS

As Id is identity ( Auto generated) column and we are trying to insert values in that, the excel data start with FName. As Fname data can not be inserted into Id, it will be ignored and rest of the data will be shift as well. The Last Name values are inserted into FName as can be seen below.

How to insert data from Excel or CSV to Table in SSMS

To avoid this, we should only select the columns in which we want to insert the data. Also select in the order in which we have the data from Excel or CSV.

Once you run the query, you will see the selected columns only. Go ahead and past the data as Step 4.
Insert data from Excel or CVS To SQL Server Table without using Import Wizard in SQL Server

Once you hit paste, data will be written to table as shown below.
How to insert data from Excel or CSV to SQL Server Table by using Edit window in SSMS

If you have few records, you might be ok with speed. If you have millions of records, inserting records by using Edit window can take long time. you might want to use Import Wizard in cases where you have huge number of records to insert into SQL Server Table.


Video Demo : How to insert Excel or CSV data into Table by using Copy Paste


No comments:

Post a Comment