question

Govindharaj.K avatar image
Govindharaj.K asked

SQL Export to Excel in different sheets using BCP

Hi, Using BCP command need to export date to Excel in different sheets Thanks.
sql-server-2008bcp
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Oleg avatar image
Oleg answered
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
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.