Monday, 5 June 2017

Bulk insert into Sql server using Excel sheet

Leave a Comment
Hi every welcome back again.I took a long time to upload a new post in this blog.I was very busy with the production work of my company project this time.In today post i will explain how to import Bulk data into Sql server tables using Excel sheet.For this we need to write any programming.Microsoft has made this facility available for their client and provided an interface in Sql server.I will ran you through that process.Like Excel import we can also import the Microsoft Access database data.We will talk about it in further posts.

Importing or Bulk insert in Sql server using Excel sheet  

Step 1: 
1.Prepare an Excel document with bulk data as shown below.


2.This Excel document contains 33 columns and 6604 rows.
Note: If you have already have table in your sql server make sure that the headers of the each column matched with column names of respective table.
If there is no table in server Sql server automatically creates a table with table name as excel sheet name(ex:Person.xls) and table columns with names of the headers of excel columns(ex:Person_Id,FirstName,LastName etc).
Step 2:
1.Open Sql server Management Studio 2012 or higher..
2.Open Object explorer and navigate to the database where you want to perform bulk insert..
3.Here i created a MitechdevDB and i will insert data into it.
4.Right click MitechdevDB--> Tasks --> Import data.

5.Next Sql server Import and Export Wizard window will appear like below.


6.In this window select Data source,Excel file path and Excel version and check the checkbox.
Note: Please close the Excel sheet that you want to insert into Sql server if it is opened..Otherwise it will show error like below.


7.After clicking next it will ask for destination and server credentials.



8.after that choose one of the below options.I chooses option1


9.I already created a Table in sql server with below schema code.
GO
/****** Object:  Table [dbo].[Person]    Script Date: 5/24/2017 7:30:49 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Person](
 [Person_Id] [int] IDENTITY(1,1) NOT NULL,
 [Prefix] [nvarchar](225) NULL,
 [FirstName] [nvarchar](225) NOT NULL,
 [MiddleName] [nvarchar](225) NULL,
 [LastName] [nvarchar](225) NOT NULL,
 [Title] [nvarchar](225) NULL,
 [Company] [nvarchar](225) NULL,
 [Industry] [nvarchar](225) NULL,
 [Address1] [nvarchar](225) NULL,
 [Address2] [nvarchar](225) NULL,
 [City] [nvarchar](225) NULL,
 [State] [nvarchar](225) NULL,
 [Zip] [int] NULL,
 [Country] [nvarchar](225) NULL,
 [Work_Phone] [nvarchar](225) NULL,
 [Home_Phone] [nvarchar](225) NULL,
 [Mobile_Phone] [bigint] NULL,
 [Fax] [nvarchar](225) NULL,
 [Email] [nvarchar](225) NOT NULL,
 [Secondary_Email] [nvarchar](225) NULL,
 [Person_Status] [int] NULL,
 [Username] [nvarchar](225) NULL,
 [Password] [nvarchar](225) NULL,
 [HintQuestion] [nvarchar](225) NULL,
 [HintAnswer] [nvarchar](225) NULL,
 [FirstLoggedIn] [int] NULL,
 [Role_Id] [int] NOT NULL,
 [Status] [int] NULL,
 [Created_By] [nvarchar](225) NULL,
 [Created_Date] [datetime] NULL,
 [Modified_By] [nvarchar](225) NULL,
 [Modified_Date] [datetime2](7) NULL,
 [Alumnus] [nvarchar](20) NULL,
 CONSTRAINT [PK_MGCS_PERSON] PRIMARY KEY CLUSTERED 
(
 [Person_Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
10.Now select Tables and views.We can edit the mappings and preview the table with data..


11.We can table schema(if we have not created table in sql server).
12.I already created a table in server.so i select Person table from Dropdown.


13.Now review the DataType Mapping in next windows.


14.Now click on Next button


15.we can also encrypt the sensitive data using password but here i am not using encryption.
16.Now finally we will get below screen that shows the status of the operation.

17.Now check the inserted data in Database...

 We got 6603 records inserted into the sql server at a time...

Conclusion

I hope this tutorial may helpful for you.In next tutorial i will come with another topic
If you are not subscribed to this blog subscribe.follow on Social networks for daily awesome updates[Facebook,twitter,google plus].

0 comments:

Post a Comment