Insert Excel Data into a SQL Server table using OPENROWSET

There are many ways to load excel data into SQL Server database, using DTS, BULK import, SSIS and many others. In this post I will go with OPENROWSET. This is very useful when we need ad-hoc connection to an OLE DB source.

Here below is the image of the excel file taken for demonstrate.

 

Here is the script to import the file into SQL Server database using OPENROWSET.

SELECT  exl.*
INTO #myExcelData
FROM OPENROWSET ('Microsoft.Ace.OLEDB.12.0'
,'Excel 12.0; Database=C:\Import\ExcelDataImport.xlsx; HDR=YES'
,'SELECT * FROM [Sheet1$]') AS exl
GO

 Now see the data imported into our table.

SELECT * FROM #myExcelData
GO

 

Here I have taken Excel 2007 as source and a SQL Server temp table as destination. Let me know if this is useful to you.

Insert or Copy Data into Table

This post is for a common situation most of us face while working with a database, which is copy/insert to a table.  We can use INSERT & SELECT to copy data into a database table in three ways.

• INSERT Statement (Direct insert data values into the table):

Using INSERT statement we can add one data row at a time to destination table.

--Example: 1
CREATE TABLE Table01
(Column1 INT PRIMARY KEY, Column2 varchar(10))
GO

INSERT INTO Table01(Column1,Column2) VALUES(101,'A')
INSERT INTO Table01(Column1,Column2) VALUES(102,'B')
INSERT INTO Table01(Column1,Column2) VALUES(103,'C')
INSERT INTO Table01(Column1) VALUES(104)
GO

SELECT Column1,Column2 FROM Table01
GO

• INSERT Statement with a SELECT Sub-Query:

If the destination table (we want to populate) is already created in the database, then using INSERT statement with a SELECT sub-query, we can copy data rows from one table to another.  The number of columns listed in INSERT statement must be same as the columns listed in SELECT statement and their data types must match.

--Example: 2
CREATE TABLE Table02
(Column1 INT IDENTITY, Column2 VARCHAR(10),
Column3 VARCHAR(8) DEFAULT('Welcome1'))
GO

INSERT INTO Table02(Column2)
SELECT Column2 FROM Table01
GO

SELECT Column1, Column2,Column3 FROM Table02
GO

• SELECT with INTO Statement:

If the destination table (we want to populate) is not available in the database, then using SELECT INTO statement also it will create the table on the fly and copy data to it from the source table.

--Example: 3
SELECT Column1, Column2, Column3
INTO NewTable
FROM Table02
WHERE Column2 IS NOT NULL
GO

SELECT * FROM NewTable
GO

Please let me know your valuable comments.