如何填充临时表，过滤它们，然后循环(SQL Server)? [英] How can I populate temporary tables, filter them, and then loop through (SQL Server)?

问题描述

我有一个需要执行的一次性操作，希望我可以用 SQL 语句(在 LINQPad 中)来完成.我需要从两个表中获取数据，然后将这些 val 插入到另一个表中.具体来说，我需要使用来自客户表的 Unit/MemberNo/CustNo 的每个唯一组合的数据填充 CustomerCategoryLog 表，并添加来自 MasterUnitsProjSales 表的相应 NewBiz 值.

I have a one-time operation I need to perform, and am hoping I can do it with a SQL statement (in LINQPad). I need to grab data from two tables, then insert those vals into another one. Specifically, I need to populate the CustomerCategoryLog table with data for each unique combination of Unit/MemberNo/CustNo from the Customers table, adding the corresponding NewBiz value from the MasterUnitsProjSales table.

伪SQL是这样的:

// First, need each unique combination of Unit, MemberNo, and CustNo from the Customers table and NewBiz from the MasterUnitsProjSales table
select distinct C.Unit, C.MemberNo, C.CustNo, M.NewBiz
into #holdingTank
from Customers C
join MasterUnitsProjSales M on M.Unit = C.Unit

// Now, I need to insert records into the CustomerCategoryLog table - New or Existing Category/Subcategory
insert into CustomerCategoryLog (Unit, MemberNo, CustNo , Category, Subcategory, BeginDate, ChangedBy, ChangedOn)
VALUES (select Unit, MemberNo, CustNo, if NewBiz = 1: 'Existing'? 'New', if NewBiz = 1: 'Existing'? 'New', Date.Now(), 'Clay Shannon', Date.Now() from #holdingTank)

如果上面那句古怪的pseudoSQL听不懂的话，这就是我需要的:

If the wacky pseudoSQL directly above is incomprehensible, this is what I need:

如果NewBiz = 1，则在Category 和Subcategory 字段中存储Existing"；否则，在这两个字段中都存储New".

if NewBiz = 1, store 'Existing' in both the Category and Subcategory fields; otherwise, store 'New' in both of those fields.

如果这需要是一个 StoredProc，它应该是什么样子的?

If this needs to be a StoredProc, what does it need to look like?

另一种选择是用 C# 编写一个实用程序来检索数据，然后循环访问结果集，有条件地将新"或现有"记录插入 CustomerCategoryLog 表中.

Another option would be to write a utility in C# to retrieve the data, then loop through the result set, conditionally inserting either 'New' or 'Existing' records into the CustomerCategoryLog table.

不过，我认为必须有一种更快的方法来使用 T-SQL 来完成它.

I'm thinking there must be a quicker way to accomplish it using T-SQL, though.

推荐答案

你所追求的是一个 case 语句...

What you are after is a case statement...

首先尝试将其作为 select 测试输出:

Try this as a select first to test output:

--// First, need each unique combination of Unit, MemberNo, and CustNo from the Customers table and NewBiz from the MasterUnitsProjSales table
select distinct C.Unit, C.MemberNo, C.CustNo, M.NewBiz
into #holdingTank
from Customers C
join MasterUnitsProjSales M on M.Unit = C.Unit

--// Now, I need to insert records into the CustomerCategoryLog table - New or Existing Category/Subcategory
--insert into CustomerCategoryLog (Unit, MemberNo, CustNo , Category, Subcategory, BeginDate, ChangedBy, ChangedOn)
select Unit, 
       MemberNo, 
       CustNo, 
       case when NewBiz = 1 then 'Existing' else 'New' end, 
       case when NewBiz = 1 then 'Existing' else 'New' end, 
       getdate(), 
       'Clay Shannon', 
       getdate()
from #holdingTank

这篇关于如何填充临时表，过滤它们，然后循环(SQL Server)?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！