-2

How can I write a single line SQL for importing 20k rows data from Excel to SQL server ? Please don't tell me about import export wizard or to write 20 line repeatedly for do the same.

Please tell me if you have any other method.

1

Have you seen the Microsoft Support article How to import data from Excel to SQL Server?

They document a variety of ways to do it. Specifically the section of "Use Distributed Queries" seems like what you're asking:

If you do not want to configure a persistent connection to the Excel workbook as a linked server, you can import data for a specific purpose by using the OPENDATASOURCE or the OPENROWSET function. The following code samples also import the data from the Excel Customers worksheet into new SQL Server tables:

SELECT * INTO XLImport3 FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C:\test\xltest.xls;Extended Properties=Excel 8.0')...[Customers$]

SELECT * INTO XLImport4 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\test\xltest.xls', [Customers$])

SELECT * INTO XLImport5 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\test\xltest.xls', 'SELECT * FROM [Customers$]')

See also OPENROWSET and linked servers in the product documentation.

Not the answer you're looking for? Browse other questions tagged or ask your own question.