question

I am not sure about the meaning of BCP command to export the data to Excel. BCP is bulk copy utility used to quickly ***insert*** the data into SQL Server database table.There are many methods to quickly export data from database table to Excel. One way is via [openrowset][1]. The sample script below declares a table variable with 10 rows and exports first 5 rows to Excel file sheet 1, and next five to sheet 2 of the same file. The Excel file needs to exist, it does not need to have data but needs to have the first row populated with column names matching the columns in the source table. Also, the network account running the script needs to have permissions to the file. For this example, please create new Excel file named test.xlsx saved in temp folder on c drive and type the column names in the first row (cells A1 through A3). Optionally, save the file at some other suitable location and modify the script accordingly. After that, close the file and run this script: -- sample source data consisting of 3 columns, 10 rows declare @t table (ID int, ColA char(1), ColB int); insert into @t values (1001, 'A', 1), (1002, 'B', 2), (1003, 'C', 3), (1004, 'D', 4), (1005, 'E', 5), (1006, 'F', 6), (1007, 'G', 7), (1008, 'H', 8), (1009, 'I', 9), (1010, 'J', 10); -- insert first 5 rows from the source to sheet1 of the Excel file insert into openrowset ( 'Microsoft.ACE.OLEDB.16.0', 'Excel 12.0;Database=c:\temp\test.xlsx;', 'select * from [Sheet1$] where 1 = 2' ) select * from @t where ID between 1001 and 1005; -- insert last 5 rows from the source to sheet2 of the Excel file insert into openrowset ( 'Microsoft.ACE.OLEDB.16.0', 'Excel 12.0;Database=c:\temp\test.xlsx;', 'select * from [Sheet2$] where 1 = 2' ) select * from @t where ID between 1006 and 1010; go After running the script, please open Excel file to check that both sheets have been populated with 5 rows of data. You might have to change the provider to match the version of ACE driver you have installed on the server hosting the SQL Server instance. Hope this helps. Oleg [1]: https://docs.microsoft.com/en-us/sql/t-sql/functions/openrowset-transact-sql