Company Sales department to statistics salesman's customers and in accordance with the name of the salesman output Excel, see the following program
--Declaring the required variables
DECLARE @sql varchar ($), @TypeID varchar (6), @MyName varchar (10)
--Each salesman should have a serial number (digital type)
Select @TypeID =min (ID) from A_cust
Select @MyName =employee_name from a_cust where [email protected]
--processing when there is a record that satisfies the sequence number
While exists (select 1 from a_cust where [email protected])
Begin
--piecing together statements that need to be executed
Set @sql = ' bcp ' SELECT * FROM (SELECT ' + ' ' customer_code ' + ' as customer code, ' + ' ' customer_name ' + ' as Customer name, ' + ' Customer_full_ Name ' + ' as customer's full name, '
+ ' Employee_code ' + ' as work number, ' + ' ' employee_name ' + ' as name, ' + ' address ' + ' as ', ' + ' ' telephone ' + ' + ' as phone, ' + ' Contact ' + ' as contacts '--display column names in the XLS file
Set @[email protected]+ ' UNION ALL Select Customer_code,customer_name,customer_full_name,employee_code,employee_name , address,telephone,contact from A_cust where id= ' +cast (@TypeID as varchar) + ') A "queryout" D:\customer\ ' +cast (@ MyName as varchar (+) + '. xls '-c-q-S "127.0.0.1"-u "sa"-P "password"-d "database name" '--query the record that satisfies the condition and save to the XLS file
--Use the xp_cmdshell system stored procedure to execute a patchwork of statements (requires the Advanced option switch to pre-open the Cmdshell component)
EXEC master. xp_cmdshell @sql
--Get the serial number of the next salesman (serial number is discontinuous)
Select @TypeID =isnull (min (ID), @TypeID + 1) from A_cust where Id>[email protected]+1
Select @MyName =employee_name from a_cust where [email protected]
End
The following program is a generated view based on each table association
Drop View A_cust
CREATE View a_cust as select Customer_code,customer_name,customer_full_name,employee_code,employee_name,d.address,d . Telephone,e.contact,a_c.id from CUSTOMER A
Left join Customer_sales b on a.customer_business_id = b.customer_id
Left join EMPLOYEE c on b.owner_emp = c.employee_id
Left Join A_c on c.employee_code = A_c.gh
Left join customer_address D on b.customer_business_id = d.customer_business_id
Left join Customer_contact e on b.customer_business_id = e.customer_business_id
where a.approvestatus = ' Y '
Insert into A_c (GH) Select Employee_code from EMPLOYEE
SELECT * FROM A_c
Select from a_cust where ADDRESS was not null
Select from CUSTOMER where customer_code = ' 0080 '
Select *from customer_sales where customer_id = ' f16dd932-0155-4a9a-4fe9-13bf5cf9277d '
SQL Server outputs Excel by category