Trying to import Excel Headers from an Excel 2003 file to MS Sql (MS SQL Server Managment Studio 2008) Table.
I have a SQL table contains two fields, ID and name. ID is auto increment.
Excel sheet contains headers and data.
I have the following code:
/* Enable SQL to work with Excel */
EXEC sp_configure 'show advanced options', 1
reconfigure
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1
reconfigure
GO
/* Insert in just the headers into mytable */
Insert into mytable (name)
SELECT TOP 1 *
FROM
OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\customers.xls;IMEX=1;HDR=No;',
'SELECT * FROM [Sheet1$]')
I turned of Headers with HDR=no, selecting the top row works.
I understand that I have selected to many columns to insert in to one row and it won't let me do so, but how can I accomplish this?
For each value in my selection insert into my table?