CODEWITHSUNDEEP
c#.netsql-serversql-server-2008
Ananth
Ananth

Reputation: 10730

Load the data from an Excel File and load into a Temp Table

I am trying to load an Excel file into a Temp table so that I can perform some manipulations in the Temp Table.

I'am using the following statement to open the excel file. But now I need to load it into a temp table.

SELECT A.*
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
                'Excel 12.0;Database=D:\b1.xlsx; hdr=yes',
                'SELECT * FROM [Co Contact$]') AS A;

What Iam trying to is some thing like this

select top 0 *
into #mytemptable
from A

I need to do it with out declaring the Temp Table without specifying the names of the columns in the excel file. (Because the excel file may contain nearly 100 columns)

Is it possible ?

Upvotes: 3

Views: 6060

Answers (1)

mbh78
mbh78

Reputation: 36

Assuming your first statement works, to put it into a temp table you need to do this:

SELECT A.*
INTO #MyTempTable
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
                'Excel 12.0;Database=D:\b1.xlsx; hdr=yes',
                'SELECT * FROM [Co Contact$]') AS A;

Then you can play with #mytemptable

Select * From #mytemptable;

Upvotes: 2

Related Questions