locked
copying data from Excel to SQL Server RRS feed

  • Question

  • Hi everyone

                       How can we copy data from EXCEL to SQL Server database using Transact SQL. I know how to copy using import/export wizard and using package, but i need to copy data from EXCEL to SQL Server using T-SQL queries.

     

    Thanks in advance

    Tuesday, April 6, 2010 6:07 AM

Answers

  • various  possibilities are described here

     

    http://support.microsoft.com/default.aspx/kb/321686?p=1


    Life is a race,tez nahi bhagoge to log kuchalke agey niklenge!
    • Proposed as answer by Kalman Toth Tuesday, April 6, 2010 7:13 AM
    • Marked as answer by Tony Tang_YJ Friday, April 9, 2010 5:06 AM
    Tuesday, April 6, 2010 6:33 AM
  • Here is an example:

     

    INSERT INTO MyTable(column1, column2, column3)
       SELECT column1, column2, column3
          FROM  OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 8.0;HDR=YES;DATABASE=D:\Data\Info.xls',Sheet1$) AS imp;

    • Marked as answer by Tony Tang_YJ Friday, April 9, 2010 5:06 AM
    Tuesday, April 6, 2010 9:46 AM

All replies

  • various  possibilities are described here

     

    http://support.microsoft.com/default.aspx/kb/321686?p=1


    Life is a race,tez nahi bhagoge to log kuchalke agey niklenge!
    • Proposed as answer by Kalman Toth Tuesday, April 6, 2010 7:13 AM
    • Marked as answer by Tony Tang_YJ Friday, April 9, 2010 5:06 AM
    Tuesday, April 6, 2010 6:33 AM
  • Here is an example:

     

    INSERT INTO MyTable(column1, column2, column3)
       SELECT column1, column2, column3
          FROM  OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 8.0;HDR=YES;DATABASE=D:\Data\Info.xls',Sheet1$) AS imp;

    • Marked as answer by Tony Tang_YJ Friday, April 9, 2010 5:06 AM
    Tuesday, April 6, 2010 9:46 AM