Import Excel Data into SQL Temp table

·      Sometimes it is necessary to import data from Excel file and insert into SQL Server table or building an automated script for inserting data into SQL Server table or building an automated script for inserting data and save into another excel file to insert later. Today I will discuss all the possibilities to solve this kind of problems. You need to follow one or more than one steps of the following depending on your requirement.

·       I have following excel sheet and I want to import all data into temp SQL table.

·         SQL Server OpenRowSet command can do data transformation easily. You can do that with following simple query in SQL.

·         I created simple temp table in SQL and import all rows from excel sheet into temp table.

INSERT  INTO [dbo].[#tblImport]

SELECT  Code, BasicSalary, GrossSalary

FROM   OPENROWSET('Microsoft.ACE.OLEDB.12.0',

            'Excel12.0;HDR=YES;Database=D:\Salary_Register_offshore.xlsx',

                          'SELECT * FROM [Sheet1$]');

Output