Using OPENROWSET

Using OPENROWSET

Easiet way to load data in a table in SQL Server for the purposes of data analysis, data load or anything you want to.

I know people will argue about using BCP, which is also a very good option. Using BCP you have good control over things like collation when importing data from different character sets or different international data sources and don't have to stick with Kanji.


INSERT dbo.xls_to_table
    (
    [ID],
    [COLUMN_1],
    [COLUMN_2],
    [COLUMN_3],
    [COLUMN_4]
    )
SELECT *
FROM OPENROWSET ( 'Microsoft.Jet.OLEDB.4.0', 'Excel 8.0; HDR = YES; IMEX = 1; DATABASE = C:\temp.xls '
SELECT ' + CONVERT(nvarchar(255), @ID) + ',
    [COLUMN_1],
    [COLUMN_2],
    [COLUMN_3],
    [COLUMN_4]
FROM [xsl_sheetname$] )