-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.

closed as unclear what you're asking by Erik Darling, mustaccio, Michael Green, Mr.Brownstone, Colin 't Hart Jan 10 at 12:38

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

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.