As Explained in my Previous Blog, we can just view all the data from Excel sheet in SQL Server Data output Viewer.
Now there was the question, How to Map Each Column from Excel Sheet to SQL Server Database Table to load the data.
A little Modification from my previous blog will do the trick of mapping.
For Example, Let me create a table in SQL Server DB.
Now My Excel sheet and Data Looks like this which I kept it on my desktop. I want to load one of column say Name into table Management Table.
The Query to Map only column 'NAME' From Excel Sheet to 'Name' column of Table Management is as follows:
After running this query, we can see Column Name is filled with Excel sheet Data leaving remaining column values as NULL.
The snippet is shown below.
Now there was the question, How to Map Each Column from Excel Sheet to SQL Server Database Table to load the data.
A little Modification from my previous blog will do the trick of mapping.
For Example, Let me create a table in SQL Server DB.
USE
[Test]
GO
CREATE
TABLE
[dbo].[Management](
[ID]
[int]
NULL,
[Name]
[varchar](100)
NULL,
[Designation]
[varchar](100)
NULL
)
ON
[PRIMARY]
|
Now My Excel sheet and Data Looks like this which I kept it on my desktop. I want to load one of column say Name into table Management Table.
The Query to Map only column 'NAME' From Excel Sheet to 'Name' column of Table Management is as follows:
Insert
into dbo.Management
(NAME)
SELECT
Name
FROM
OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel
12.0 Xml;HDR=YES;
Database=C:\Users\dhinakaran\Desktop\Src.xlsx',
'SELECT
* FROM [Sheet1$]');
|
After running this query, we can see Column Name is filled with Excel sheet Data leaving remaining column values as NULL.
The snippet is shown below.
Comments
Post a Comment