Export multiple Excel files in batches based on conditions in SQL tables

Exporting data in SQL Server tables to multiple Excel files in batches by conditions is what we will introduce in this article. In an SQL Server database operation, you need to export a table with hundreds of thousands of rows of data into an xls file by category. Each category has one xls file. Export and save the data in the data table as xls. Use SSIS or search and save it

Exporting data in SQL Server tables to multiple Excel files in batches by conditions is what we will introduce in this article. In an SQL Server database operation, you need to export a table with hundreds of thousands of rows of data into an xls file by category. Each category has one xls file. Export and save the data in the data table as xls. Use SSIS or search and save it

Exporting data in SQL Server tables to multiple Excel files in batches by conditions is what we will introduce in this article. In an SQL Server database operation, you need to export a table with hundreds of thousands of rows of data into an xls file by category. Each category has one xls file. Export and save the data in the data table as xls. You can use SSIS or save the data as xls. However, there are hundreds of categories in this table. If you use SSIS or save the query as one by one, the workload is huge. After thinking about it, I thought about using the while LOOP query and the bcp export method.

The following code is used:

-- Declare the required variable
Declare @ SQL varchar (600), @ TypeID int
-- Confirm the ID of the first category
Select @ TypeID = min (TypeID) from t_TestTable
-- Processing is performed when a record with the specified category ID exists.
While exists (select 1 from t_TestTable where TypeID = @ TypeID)
Begin
-- Piece together the statements to be executed
Set @ SQL = 'bcp "select * from (select '+ ''' column name 1 ''' + 'as column name 1, '+ ''' column name 2 ''' + 'as column name 2,' + ''' column name 3 ''' + 'as column name 3' -- display the column name in the xls file
Set @ SQL = @ SQL + 'Union all select column name 1, column name 2, column name 3 from t_TestTable where TypeID = '+ cast (@ TypeID as varchar (8) + ') a "queryout" F: datafiletypedata' + cast (@ TypeID as varchar(8))+'.xls "-c-q-S" ServerName "-U" sa "-P" SAPASSWORD "-d" DBName "'-- Query conditions that meet the conditions record and save it to the xls file
-- Execute the assembled statement using the stored procedure of the xp_cmdshell System (enable the cmdshell component in advance using the advanced Option Switch)
Exec master .. xp_mongoshell @ SQL
-- Obtain the ID of the next category (the category ID is discontinuous)
Select @ TypeID = isnull (min (TypeID), @ TypeID + 1) from t_TestTable where TypeID >=@ TypeID + 1
End

The above is how to export data in SQL Server tables to multiple Excel files in batches according to conditions. This article will introduce it here. I hope this introduction will help you gain some benefits!