SQL Server: Import Tab From Excel

--FOR XLS FILES (ALL COLUMNS EXAMPLE)

SELECT * INTO NEW_TABLE_NAME

FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',

'Excel 8.0;Database=FILESOURCE&NAME.xls', 'SELECT * FROM [TABNAME$]')

--FOR XLSX FILES (SPECIFIC COLUMNS EXAMPLE)

SELECT

[COLUMN_NAME],

[ANOTHER_COLUMN_NAME]

INTO NEW_TABLE_NAME

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

'Excel 8.0;Database=FILESOURCE&NAME.xlsx', 'SELECT * FROM [TABNAME$]')

--LIVE EXAMPLE FOR FY11 ES, XLSX FILE

SELECT

[Legacy Sales Order Identifier],

LEFT([Legacy Sales Order Identifier],9) AS [ES Transaction Number],

'Int ES' AS [Sales Channel Name],

[Sales Motion]

INTO ESTS_Data

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

'Excel 8.0;Database=C:\location\filename.xlsx', 'SELECT * FROM [FY11 ES Data 4-14-11$]')

Read More