我有一个非常复杂的SQL查询，需要每周一早上（早）运行。
我得到了SQL查询来提供我需要的数据，但我不知道如何让查询将结果插入表中。
申报声明导致了问题（我认为）
有人有什么想法吗？理想情况下，我希望设置一个只运行并附加到表中的作业。
谢谢。K.
DECLARE @StartDateTime DATETIME;
DECLARE @EndDateTime DATETIME;
DECLARE @ReportOn CHAR(1);
DECLARE @Group1 VARCHAR(25);
DECLARE @Group2 VARCHAR(25);
DECLARE @Date1Grouping VARCHAR(25);
DECLARE @Date2Grouping VARCHAR(25);
DECLARE @DivisionID VARCHAR(10);
DECLARE @GroupSubsidiaries BIT;
SET @StartDateTime = '3/06/2013' ;
SET @EndDateTime = '3/12/2013' ;
SET @ReportOn = 'S' ;
SET @Group1 = 'Due Date' ;
SET @Group2 = 'Sale Date' ;
SET @Date1Grouping = 'Yearly' ;
SET @Date2Grouping = 'Daily' ;
SET @GroupSubsidiaries = 1 ;
DECLARE @GLEntries TABLE (ID INT PRIMARY KEY, Amount FLOAT, EntryDateTime DATETIME, GLClassificationType INT,
GroupID INT, GLAccountID INT, GLAccountClassTypeID INT, AccountID INT,
TransHeaderID INT, TransDetailID INT, DivisionID INT,
Classification INT, IsTaxable BIT, TaxClassID INT,
GoodsItemID INT, GoodsItemClassTypeID INT);
-- 'C' used for Closed Reports
IF ( @ReportOn = 'C' )
INSERT INTO @GLEntries
SELECT GL.ID, GL.Amount, GL.EntryDateTime, GL.GLClassificationType,
GL.GroupID, GL.GLAccountID, GL.GLAccountClassTypeID, GL.AccountID,
GL.TransactionID, GL.TransDetailID, GL.DivisionID,
GL.Classification, GL.IsTaxable, GL.TaxClassID,
GL.GoodsItemID, GL.GoodsItemClassTypeID
FROM GL WITH(NOLOCK)
WHERE (GL.GLAccountID <> 53)
AND EXISTS( SELECT 1
FROM TransHeader
WHERE ClosedDate BETWEEN @StartDateTime and @EndDateTime
AND ID = GL.TransactionID )
ELSE
INSERT INTO @GLEntries
SELECT GL.ID, GL.Amount, GL.EntryDateTime, GL.GLClassificationType,
GL.GroupID, GL.GLAccountID, GL.GLAccountClassTypeID, GL.AccountID,
GL.TransactionID, GL.TransDetailID, GL.DivisionID,
GL.Classification, GL.IsTaxable, GL.TaxClassID,
GL.GoodsItemID, GL.GoodsItemClassTypeID
FROM GL WITH(NOLOCK)
WHERE (GL.GLAccountID <> 53)
AND GL.EntryDateTime between @StartDateTime and @EndDateTime
;
SELECT Orders.*, Totals.*,
( SELECT -Sum(Amount)
FROM @GLEntries GL1
LEFT JOIN TransHeader TH ON (TH.ID = GL1.TransHeaderID)
WHERE GL1.GLClassificationType = 2005)
as TotalTaxes,
@StartDateTime as theStartDate,
@EndDateTime as theEndDate,
-- Selects Group1 based on @Group1
CASE
WHEN @Group1 = 'Product' THEN Product
WHEN @Group1 = 'None' THEN 'None'
WHEN @Group1 = 'Due Date' THEN
CASE
WHEN @Date1Grouping = 'Daily' THEN Convert( VARCHAR(25), Orders.DueDate, 112)
WHEN @Date1Grouping = 'Weekly' THEN Convert( VARCHAR(25), DateAdd(d, -DatePart(dw,Orders.DueDate) + 1, Orders.DueDate), 112 )
WHEN @Date1Grouping = 'Monthly' THEN Cast( DatePart(yyyy, Orders.DueDate) * 100 + DatePart(mm,Orders.DueDate) AS VARCHAR(7) )
WHEN @Date1Grouping = 'Yearly' THEN Cast( DatePart(yyyy, Orders.DueDate) AS VARCHAR(4) )
END
WHEN @Group1 = 'Sales Date' THEN
CASE
WHEN @Date1Grouping = 'Daily' THEN Convert( VARCHAR(25), Orders.EntryDateTime, 112 )
WHEN @Date1Grouping = 'Weekly' THEN Convert( VARCHAR(25), DateAdd(d, -DatePart(dw,Orders.EntryDateTime) + 1, Orders.EntryDateTime), 112 )
WHEN @Date1Grouping = 'Monthly' THEN Cast( DatePart(yyyy, Orders.EntryDateTime) * 100 + DatePart(mm, Orders.EntryDateTime) AS VARCHAR(7) )
WHEN @Date1Grouping = 'Yearly' THEN Cast( DatePart(yyyy, Orders.EntryDateTime) AS VARCHAR(4) )
END
WHEN @Group1 = 'Primary Salesperson' THEN Salesperson1LastName + Salesperson1FirstName
WHEN @Group1 = 'Customer Origin' THEN OriginName
WHEN @Group1 = 'Industry' THEN IndustryName
WHEN @Group1 = 'Order Origin' THEN OrderOriginName
WHEN @Group1 = 'Postal Code' THEN PostalCode
WHEN @Group1 = 'Postal Code 3 Digit' THEN PostalCode3Digit
WHEN @Group1 = 'Product Category' THEN ProductCategory
WHEN @Group1 = 'Account' THEN AccountName
WHEN @Group1 = 'Company Name' THEN CompanyName
WHEN @Group1 = 'Company Frequency' THEN CompanyName
WHEN @Group1 = 'Company Volume' THEN CompanyName
WHEN @Group1 = 'GL Department' THEN GLDepartment
WHEN @Group1 = 'Industry (Parent)' THEN IndustryParent
WHEN @Group1 = 'Company Origin (Parent)' THEN CompanyOriginParent
WHEN @Group1 = 'Order Origin (Parent)' THEN OrderOriginParent
WHEN @Group1 = 'Region' THEN RegionName
END AS Group1,
CASE
WHEN @Group2 = 'Product' THEN Product
WHEN @Group2 = 'None' THEN 'None'
WHEN @Group2 = 'Due Date' THEN
CASE
WHEN @Date2Grouping = 'Daily' THEN Convert( VARCHAR(25), Orders.DueDate, 112 )
WHEN @Date2Grouping = 'Weekly' THEN Convert( VARCHAR(25), DateAdd(d, -DatePart(dw, Orders.DueDate) + 1, Orders.DueDate), 112 )
WHEN @Date2Grouping = 'Monthly' THEN Cast( DatePart(yyyy, Orders.DueDate) * 100 + DatePart(mm, Orders.DueDate) AS VARCHAR(7) )
WHEN @Date2Grouping = 'Yearly' THEN Cast( DatePart(yyyy, Orders.DueDate) AS VARCHAR(4) )
END
WHEN @Group2 = 'Sales Date' THEN
CASE
WHEN @Date2Grouping = 'Daily' THEN Convert( VARCHAR(25), Orders.EntryDateTime, 112 )
WHEN @Date2Grouping = 'Weekly' THEN Convert( VARCHAR(25), DateAdd(d, -DatePart(dw, Orders.EntryDateTime) + 1, Orders.EntryDateTime), 112 )
WHEN @Date2Grouping = 'Monthly' THEN Cast( DatePart(yyyy, Orders.EntryDateTime) * 100 + DatePart(mm, Orders.EntryDateTime) AS VARCHAR(7) )
WHEN @Date2Grouping = 'Yearly' THEN Cast( DatePart(yyyy, Orders.EntryDateTime) AS VARCHAR(4) )
END
WHEN @Group2 = 'Primary Salesperson' THEN Salesperson1LastName + Salesperson1FirstName
WHEN @Group2 = 'Customer Origin' THEN OriginName
WHEN @Group2 = 'Industry' THEN IndustryName
WHEN @Group2 = 'Order Origin' THEN OrderOriginName
WHEN @Group2 = 'Postal Code' THEN PostalCode
WHEN @Group2 = 'Postal Code 3 Digit' THEN PostalCode3Digit
WHEN @Group2 = 'Product Category' THEN ProductCategory
WHEN @Group2 = 'Account' THEN AccountName
WHEN @Group2 = 'Company' THEN CompanyName
WHEN @Group2 = 'GL Department' THEN GLDepartment
WHEN @Group2 = 'Region' THEN RegionName
END AS Group2
FROM ( SELECT 1 AS StoreID,
fx_Return.DivisionID,
EmployeeGroup.DivisionName,
fx_Return.TransHeaderID,
fx_Return.OrderNumber,
fx_Return.InvoiceNumber,
fx_Return.CompanyName,
-fx_Return.GLAmount AS Amount,
GLAccount.AccountName,
fx_Return.StatusText,
fx_Return.Classification,
fx_Return.GLClassificationType,
fx_Return.Description,
fx_Return.EntryDateTime,
fx_Return.DiscountPrice,
fx_Return.SaleDate,
fx_Return.DueDate,
fx_Return.BuiltDate,
fx_Return.OrderCreatedDate,
fx_Return.ClosedDate,
fx_Return.AccountID,
fx_Return.GLDepartment,
fx_Return.SalesPerson1ID,
SalesPerson1.FirstName AS SalesPerson1FirstName,
SalesPerson1.LastName AS SalesPerson1LastName,
Origin.ItemName AS OriginName,
CASE
WHEN Origin.ParentID = 11 THEN Origin.ItemName
ELSE CompanyOriginParent.ItemName
END AS CompanyOriginParent,
Industry.ItemName AS IndustryName,
CASE
WHEN IndustryParent.ID = 10 THEN Industry.ItemName
ELSE IndustryParent.ItemName
END AS IndustryParent,
fx_Return.OrderOriginName,
CASE
WHEN OrderOrigin.ParentID = 13 THEN OrderOrigin.ItemName
ELSE OrderOriginParent.ItemName
END AS OrderOriginParent,
Address.PostalCode,
SUBSTRING(Address.PostalCode,1,3) AS PostalCode3Digit,
Product.ItemName AS Product,
ProductCategory.ElementName AS ProductCategory,
ProductSubCategory.ElementName AS ProductSubCategory,
Region.ItemName AS RegionName
FROM ( SELECT GL2.EntryDateTime,
SUM( CASE
WHEN @ReportOn NOT IN ('P','B') THEN GL2.Amount
WHEN GL2.GLAccountID IN (11, 12) THEN -GL2.Amount
WHEN GL2.GLClassificationType IN (4000, 2005) THEN GL2.Amount
ELSE 0
END ) AS GLAmount,
GL2.GLAccountID,
CASE
WHEN GL2.GLClassificationType IN (4000,5002)
AND COALESCE(GL2.Classification,-1) NOT IN (100,200,300,400,500) THEN 100
ELSE GL2.Classification
END AS Classification,
GL2.GLClassificationType,
GL2.DivisionID,
GL2.IsTaxable,
GL2.TaxClassID,
GL2.GroupID,
TransHeader.StatusText,
CAST(TransHeader.Description AS VARCHAR(30)) AS Description,
TransHeader.DueDate,
TransHeader.OrderOriginName,
-- TransHeader Related Fields
GL2.TransHeaderID,
TransHeader.OrderNumber,
COALESCE( TransHeader.InvoiceNumber, TransHeader.OrderNumber) as InvoiceNumber,
TransHeader.CreditMemoOrderID,
TransHeader.Salesperson1ID,
TransHeader.OrderOriginID,
TransHeader.InvoiceAddressID as OrderInvoiceAddressID,
TransHeader.PromotionID,
Transheader.DiscountPrice,
TransHeader.PricingLevelID,
TransHeader.ClosedDate,
TransHeader.OrderCreatedDate,
TransHeader.SaleDate,
TransHeader.BuiltDate,
-- TransDetail Related Fields
GL2.TransDetailID,
TransDetail.LineItemNumber,
TransDetail.Quantity,
GL2.GoodsItemID,
GL2.GoodsItemClassTypeID,
TransDetail.GoodsItemCode,
Station.StationName as GLDepartment,
-- Account Related Fields
GL2.AccountID,
ParentAccount.CompanyName,
ParentAccount.AccountNumber,
ParentAccount.PricingPlanTypeID,
ParentAccount.IndustryID,
ParentAccount.RegionID,
ParentAccount.OriginID as CompanyOriginID,
ParentAccount.Marketing3ID,
ParentAccount.BillingAddressID as CompanyBillingAddressID
FROM @GLEntries GL2
LEFT JOIN TransHeader WITH(NOLOCK) ON (TransHeader.ID = GL2.TransHeaderID)
LEFT JOIN Account WITH(NOLOCK) ON (Account.ID = GL2.AccountID)
LEFT JOIN Account ParentAccount WITH(NOLOCK) ON ParentAccount.ID = ( CASE WHEN @GroupSubsidiaries = 1 THEN COALESCE(Account.ParentID, Account.ID) ELSE Account.ID END )
LEFT JOIN TransDetail WITH(NOLOCK) ON (TransDetail.ID = GL2.TransDetailID)
LEFT JOIN Station WITH(NOLOCK) ON TransDetail.GLDepartmentID = Station.ID
WHERE ( GL2.GLClassificationType IN (4000)
OR ( @ReportOn = 'B' AND GL2.GLAccountID in (12) )
OR ( @ReportOn = 'P' AND GL2.GLAccountID in (11,12) )
)
GROUP BY GL2.EntryDateTime,
GL2.GLAccountID,
GL2.GLAccountClassTypeID,
GL2.Classification,
GL2.GLClassificationType,
GL2.DivisionID,
GL2.IsTaxable,
GL2.TaxClassID,
GL2.GroupID,
-- TransHeader Related Fields
GL2.TransHeaderID,
TransHeader.OrderNumber,
COALESCE( TransHeader.InvoiceNumber, TransHeader.OrderNumber ),
TransHeader.CreditMemoOrderID,
TransHeader.Salesperson1ID,
TransHeader.OrderOriginID,
TransHeader.InvoiceAddressID,
TransHeader.PromotionID,
TransHeader.PricingLevelID,
Transheader.DiscountPrice,
TransHeader.OrderCreatedDate,
TransHeader.SaleDate,
TransHeader.ClosedDate,
TransHeader.BuiltDate,
TransHeader.StatusText,
TransHeader.DueDate,
TransHeader.OrderOriginName,
CAST(TransHeader.Description AS VARCHAR(30)),
-- TransDetail Related Fields
GL2.TransDetailID,
TransDetail.LineItemNumber,
TransDetail.Quantity,
GL2.GoodsItemID,
GL2.GoodsItemClassTypeID,
TransDetail.GoodsItemCode,
Station.StationName,
-- Account Related Fields
GL2.AccountID,
ParentAccount.CompanyName,
ParentAccount.AccountNumber,
ParentAccount.PricingPlanTypeID,
ParentAccount.IndustryID,
ParentAccount.RegionID,
ParentAccount.OriginID,
ParentAccount.Marketing3ID,
ParentAccount.BillingAddressID ) AS fx_Return
LEFT JOIN EmployeeGroup WITH(NOLOCK) ON EmployeeGroup.ID = fx_Return.DivisionID
LEFT JOIN MarketingListItem AS Origin WITH(NOLOCK) ON Origin.ID = fx_Return.CompanyOriginID
LEFT JOIN MarketingListItem AS CompanyOriginParent WITH(NOLOCK) ON Origin.ParentID = CompanyOriginParent.ID
AND Origin.ParentClassTypeID = CompanyOriginParent.ClassTypeID
LEFT JOIN MarketingListItem AS Industry WITH(NOLOCK) ON Industry.ID = fx_Return.IndustryID
LEFT JOIN MarketingListItem AS IndustryParent WITH(NOLOCK) ON Industry.ParentID = IndustryParent.ID
AND Industry.ParentClassTypeID = IndustryParent.ClassTypeID
LEFT JOIN MarketingListItem AS OrderOrigin WITH(NOLOCK) ON fx_Return.OrderOriginID = OrderOrigin.ID
LEFT JOIN MarketingListItem AS OrderOriginParent WITH(NOLOCK) ON OrderOrigin.ParentID = OrderOriginParent.ID
AND OrderOrigin.ParentClassTypeID = OrderOriginParent.ClassTypeID
LEFT JOIN Address WITH(NOLOCK) ON fx_Return.CompanyBillingAddressID = Address.ID
LEFT JOIN CustomerGoodsItem AS Product WITH(NOLOCK) ON fx_Return.GoodsItemID = Product.ID
AND Product.ClassTypeID=fx_Return.GoodsItemClassTypeID
LEFT JOIN PricingElement AS ProductCategory WITH(NOLOCK) ON Product.CategoryID = ProductCategory.ID
AND Product.CategoryClassTypeID = ProductCategory.ClassTypeID
LEFT JOIN PricingElement AS ProductSubCategory WITH(NOLOCK) ON ProductCategory.ParentID = ProductSubCategory.ID
AND ProductCategory.ParentClassTypeID = ProductSubCategory.ClassTypeID
LEFT JOIN Employee AS Salesperson1 WITH(NOLOCK) ON fx_Return.SalesPerson1ID = Salesperson1.ID
LEFT JOIN GLAccount WITH(NOLOCK) ON fx_Return.GLAccountID = GLAccount.ID
LEFT JOIN MarketingListItem AS Region WITH(NOLOCK) ON fx_Return.RegionID = Region.ID
) AS Orders
LEFT JOIN ( SELECT SUM( CASE
WHEN @ReportOn <> 'P' THEN -GL3.Amount
WHEN GL3.GLAccountID IN (11, 12, 51) THEN GL3.Amount
WHEN GL3.GLClassificationType IN (4000, 2005) THEN -GL3.Amount
ELSE 0
END ) AS CompanyTotalAmount,
COUNT(DISTINCT TransHeader.ID) AS CompanyOrderCount,
GL3.AccountID AS TotalsAccountID
FROM @GLEntries GL3
LEFT JOIN TransHeader WITH(NOLOCK) ON (TransHeader.ID = GL3.TransHeaderID)
WHERE ( GL3.GLClassificationType in (4000) -- 5002) --Kyle/Scott, we have no clue why this was included, can't think of any expense accounts you'd ever show as a sale
OR
( @ReportOn = 'B' AND GL3.GLAccountID in (12) )
OR
( @ReportOn = 'P' AND GL3.GLAccountID in (11, 12) )
)
GROUP BY GL3.AccountID ) AS Totals ON Totals.TotalsAccountID = Orders.AccountID
WHERE OrderNumber IS NOT NULL
1）确定-因此您需要创建一个具有与选择列表中相同列和数据类型的表（SELECT Orders.*, Totals.*…）
2） 您必须在select之前包含insert语句，类似于以下内容insert StagingTableName
select Orders.*, Totals.*...
3） 创建一个包含所有代码的存储过程。
4） 使用SQL Server代理作业执行存储过程。这个链接应该会有所帮助。