Double WHILE loop?

Question

总体情况是，我需要提取一堆不同的个人数据并按站点汇总。最终的结果看起来像这样:

Site1
   Person1 general info
   Person1 sales info
   Person1 customer info
   Person1 vendor info
   Person2 general info
   Person2 sales info
   Person2 customer info
   Person2 vendor info
   Person3 general info
   .
   .
   .
Site2
   Person1 general info
   Person1 sales info
   Person1 customer info
   Person1 vendor info
   Person2 general info
   Person2 sales info
   Person2 customer info
   Person2 vendor info
   Person3 general info
   .
   .
   .
Site 4
   Person1 general info
  ...etc

每个站点可能有也可能没有相同的人员。这些站点都在一个表中。Person信息的4个类别都在单独的表中。所有人的一般信息在表1中，包括站点编号。所有人员的销售信息在表2中，还包括每个人的站点编号。客户和供应商信息都在单独的表中，但只引用员工的personID(而不是站点)。

此外，为了bcp输出我的结果，我从这个查询中获取结果并将其放入一个临时表中，然后将每个结果附加到最终的文本文件中。

我在这里的代码使结果看起来像这样:

Site1
Person1 general info
Person1 sales info
Person1 customer info
Person1 vendor info
Site4
Person2 general info
Person2 sales info
Person2 customer info
Person2 vendor info
Site2
Person3 general info
...etc

这是一个网站，一个人的代码:

DECLARE @salespersonid INT
DECLARE @siteNum VARCHAR(14)
DECLARE @count INT
DECLARE @rID INT
DECLARE @SQLCmd as VARCHAR(500)
DECLARE @fileName VARCHAR(50)
set @count = (select count(*) from tmp_salesSDrec)
set @rID = 1 --because siteid is sequential and unique in tmp_salesSDrec
WHILE @count > 0
BEGIN
set @siteNum = (select siteNumber from tmp_salesSDrec where rID = @rID)
set @salespersonid = (select salespersonid from tmp_sales01rec where rID = @rID)
--drop the tmp_salestemp table
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tmp_salestemp]') AND TYPE IN (N'U'))
DROP TABLE [dbo].[tmp_salestemp]
SELECT field1 into tmp_salestemp
FROM
(
     --SDrec
          SELECT CONCAT(recordID,',',CAST(LayoutVersion as VARCHAR(3)),',',submissionType,',',salesYear,',',siteType,',',',',',',',',',',','
                              ,CAST(siteCode as VARCHAR(6)),',',',',
                         TPSCode,',',physicalsiteName,',',siteAddr,',',siteCity,',',sicustate,',',siteZip,',',',',',',adminContFirstName,',',adminContLastName,',',
                         adminContPhone,',',adminContEmail,',',techContFirstName,',',techContLastName,',',techContPhone,',',techContEmail,',',CAST(numreports as VARCHAR(4))
                         ,',',CAST(ConversionFactor as VARCHAR(6)),',',',',calTypeCY,',',',',',',calTypeCY1,',',',',',',calTypeCY2,',',',',',',calTypeCY3,',',',',',',',',',',',',','
                              ,CAST(MatchingName as VARCHAR(2)),',',',',
                         extractDate,',',eor)
          as field1, 1 as sortOrder, recordID
          from tmp_salesSDrec
          where siteNumber = @siteNum
  UNION ALL
     --01rec
          SELECT CONCAT(recordID,',',',',CAST(siteATPCode as VARCHAR(6)),',',empNumber,',',stateNumber,',',',',',',',',firstName,',',',',lastName
                         ,',',address_1,',',',',city,',',[state],',',zip,',',',',CONVERT(VARCHAR(8),dob,112),',',gender,',',salesgrade,',',',',',',salesgraddate,',',',',',',salesrank,',',',',',',',',',',
                         partnerYouth,',',siteConsent,',',eor)
          as field1, 2 as sortOrder, recordID
          from tmp_sales01rec
          where salespersonid = @salespersonid and siteNumber = @siteNum
  UNION ALL
     --02rec
          select CONCAT(recordID,',',salesgrade,',',siteAttended,',',siteATPCode,',',',',salesYear,',',',',term,',',blockSales,',',workInProgress,',',
                         CAST(sectorID as VARCHAR(25)),',',sectorName,',',',',',',',',PrepInd,',',salesAtmpt,',',salesEarned,',',sectorsalesgrade,',',LOWER(area),',',eor)
          as field1, 3 as sortOrder, recordID
          from tmp_sales02rec
          where salespersonid = @salespersonid and siteNumber = @siteNum
    UNION ALL
     --03rec
          select CONCAT(recordID,',',custType,',',custDate,',',subcustName,',',custScore,',',eor)
          as field1, 4 as sortOrder, recordID
          from tmp_sales03rec
          where salespersonid = @salespersonid
    UNION ALL
     --04rec
          select CONCAT(recordID,',',vendorType,',',otherVendorType,',',vendorDate,',',vendorStatus,',',eor)
          as field1, 5 as sortOrder, recordID
          from tmp_sales04rec
          where salespersonid = @salespersonid
) sq
order by sortOrder, recordID;
SELECT @SQLCmd = 'bcp "select * from [sales].dbo.tmp_salestemp" queryout "c:bcpTemp.csv" -w -t -T -S && type c:bcpTemp.csv >> c:sales_2015.csv' ;
EXEC master..xp_cmdshell @SQLCmd;
     set @rID = @rID + 1;
     set @count = @count - 1;

END

我想我需要为每个salespersonid添加第二个while循环，然后为站点添加原始循环。

有没有人有任何建议或更好的方法来完成这个?

Answer 1

您应该能够编写没有任何while循环的代码。使用纯基于集合的查询会快得多。将你的查询更改为如下内容，并将其全部导出。

SELECT field1 into tmp_salestemp
FROM
(
     --SDrec
          SELECT CONCAT(recordID,',',CAST(LayoutVersion as VARCHAR(3)),',',submissionType,',',salesYear,',',siteType,',',',',',',',',',',','
                              ,CAST(siteCode as VARCHAR(6)),',',',',
                         TPSCode,',',physicalsiteName,',',siteAddr,',',siteCity,',',sicustate,',',siteZip,',',',',',',adminContFirstName,',',adminContLastName,',',
                         adminContPhone,',',adminContEmail,',',techContFirstName,',',techContLastName,',',techContPhone,',',techContEmail,',',CAST(numreports as VARCHAR(4))
                         ,',',CAST(ConversionFactor as VARCHAR(6)),',',',',calTypeCY,',',',',',',calTypeCY1,',',',',',',calTypeCY2,',',',',',',calTypeCY3,',',',',',',',',',',',',','
                              ,CAST(MatchingName as VARCHAR(2)),',',',',
                         extractDate,',',eor)
          as field1, 1 as sortOrder, recordID, tmp_salesSDrec.siteNumber, convert(int,null) salespersonid
          from tmp_salesSDrec
  UNION ALL
     --01rec
          SELECT CONCAT(recordID,',',',',CAST(siteATPCode as VARCHAR(6)),',',empNumber,',',stateNumber,',',',',',',',',firstName,',',',',lastName
                         ,',',address_1,',',',',city,',',[state],',',zip,',',',',CONVERT(VARCHAR(8),dob,112),',',gender,',',salesgrade,',',',',',',salesgraddate,',',',',',',salesrank,',',',',',',',',',',
                         partnerYouth,',',siteConsent,',',eor)
          as field1, 2 as sortOrder, recordID, tmp_salesSDrec.siteNumber, salespersonid
          from tmp_sales01rec 
          join tmp_salesSDrec 
          on tmp_sales01rec.siteNumber = tmp_salesSDrec.siteNumber
  UNION ALL
     --02rec
          select CONCAT(recordID,',',salesgrade,',',siteAttended,',',siteATPCode,',',',',salesYear,',',',',term,',',blockSales,',',workInProgress,',',
                         CAST(sectorID as VARCHAR(25)),',',sectorName,',',',',',',',',PrepInd,',',salesAtmpt,',',salesEarned,',',sectorsalesgrade,',',LOWER(area),',',eor)
          as field1, 3 as sortOrder, recordID, tmp_salesSDrec.siteNumber, salespersonid
          from tmp_sales01rec 
          join tmp_salesSDrec 
          on tmp_sales01rec.siteNumber = tmp_salesSDrec.siteNumber
    UNION ALL
     --03rec
          select CONCAT(recordID,',',custType,',',custDate,',',subcustName,',',custScore,',',eor)
          as field1, 4 as sortOrder, recordID, tmp_salesSDrec.siteNumber, salespersonid
          from tmp_sales01rec 
          join tmp_salesSDrec 
          on tmp_sales01rec.siteNumber = tmp_salesSDrec.siteNumber
    UNION ALL
     --04rec
          select CONCAT(recordID,',',vendorType,',',otherVendorType,',',vendorDate,',',vendorStatus,',',eor)
          as field1, 5 as sortOrder, recordID, tmp_salesSDrec.siteNumber, salespersonid
          from tmp_sales01rec 
          join tmp_salesSDrec 
          on tmp_sales01rec.siteNumber = tmp_salesSDrec.siteNumber
) sq
order by tmp_salesSDrec.siteNumber, salespersonid, sortOrder, recordID;

我没有你的任何数据，所以我无法测试这个，所以它可能需要一些调整