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.
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
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
OPENROWSETfunction. 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.