How can I populate temporary tables, filter them, and then loop through (SQL Server)?

Question

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.

Pseudo-SQL is something like this:

// 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)

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

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

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

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.

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

score 3 · Accepted Answer · answered Mar 09 '17 at 21:55

3

What you are after is a case statement...

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

answered Mar 09 '17 at 21:55

Dave Cullum

6,912
1
16
29

1

NB You can use `SYSTEM_USER` in place of hard coding your name to return the SQL login name. – Dave Cullum Mar 09 '17 at 21:57
I ran the first select block first, and then tried both together, and get, "Error 2714: There is already an object named '#holdingTank' in the database." When I then prepended a "drop #holdingTank" on the first line of it all, I got, "Error 343: Unknown object type '#holdingTank' used in a CREATE, DROP, or ALTER statement." It is not visible in my list of tables, though... – B. Clay Shannon Mar 09 '17 at 22:30
Okay, I forgot to insert "table" between "drop" and the name of the temp table. – B. Clay Shannon Mar 09 '17 at 22:32
2

You can add `if object_id('tempdb..#holdingTank') is not null drop table #holdingTank` at the top to handle the drop, it will fire as needed. – Dave Cullum Mar 10 '17 at 02:54

How can I populate temporary tables, filter them, and then loop through (SQL Server)?

1 Answers1

Linked