Home Forums SQL Server 2005 SQL Server 2005 General Discussion Better Excel Insert procedure? RE: Better Excel Insert procedure?

  • April 15, 2010 at 10:42 am

    #1152291

    Jayded (4/15/2010)

    INSERT INTO MAPPINGTABLE

    SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\Documents and Settings\user\My Documents\Length Adjustments\Mapping\Mapping Table.xls', 'SELECT * FROM [Sheet1$]')

    Try:

    INSERT INTO MAPPINGTABLE (Col1, Col2, Col3)

    SELECT Col1, Col2, Col3

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

    'Excel 8.0;Database=C:\Documents and Settings\user\My Documents\Length

    Adjustments\Mapping\Mapping Table.xls', 'SELECT * FROM [Sheet1$]')

    This way you can grab whatever columns you want from the spreadsheet, and put them into specific column in the table.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2