TSQL - Nested Loop Inserts

coldchillin
coldchillin used Ask the Experts™
on
This is also just a question in general that I'd like to know, but, I need to perform a bulk insert, which I can accomplish in ColdFusion. The problem is that it times out, and I'd rather do this on SQL Server anyway.

Query 1 - Get Client IDs

Query 2 - Get Location IDs

Relationship 1-M Q1,Q2

code ---
get client query

loop client query
   get location query
        loop location query
             insert into ClientLocation table
             values (clientID,LocationID)
              ...
         end loop
    ...
end loop


I'm familiar with a basic bulk insert in sql


insert into (col1,col2)
select this, that from table
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
if it times out, it is likely missing indexes.

you could do it at once:
insert into (col1,col2)
select this, that 
from query1 q1
join relationship r
  on r.clientid = q1.clientid
join query2 q2
  on q2.locationid = r.locationid 

Open in new window


still, you might required indexes to make sure the query runs fast.

Author

Commented:
Let me restate, I may not have given the right info. so I'll use accurate table names.

I have two tables, each with an ID, with a 1 to many relationship. I'll be inserting into a 3rd table, which holds the two ids.

Program
ProgramID - unique
GroupCode

Customer
CustomerID - unique
GroupCode

I'll join the tables on GroupCode and I'll list some sample data and desired results

Program
1, ABA
2, ABA
3, BBB
4, CCC

Customer
10, ABA
11, ABA
12, ABA
13, BBB
14, BBB
15, CCC
16, CCC

ProgramCustomer
1, 10
1, 11
1, 12
2, 10
2, 11
2, 12
3, 13
3, 14
4, 15
4, 16
Commented:
This should work..

INSERT INTO ProgramCustomer (ProgramID, CustomerID)
SELECT p.ProgramID, c.CustomerID
FROM Program p
INNER JOIN Customer c ON c.GroupCode = p.GroupCode
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009
Commented:
I see.
yes, I think kaminda's code should be what you need.
unless you want to avoid to insert duplicates (next step)
INSERT INTO ProgramCustomer (ProgramID, CustomerID)
SELECT p.ProgramID, c.CustomerID
FROM Program p
INNER JOIN Customer c ON c.GroupCode = p.GroupCode
WHERE NOT EXISTS( SELECT NULL FROM ProgramCustomer pc WHERE pc.ProgramID = p.ProgramID and pc.CustomerID = c.CustomerID )

Open in new window

Author

Commented:
Thanks all (I will want to avoid duplicates)!

That seems like what I'm looking for, thanks!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial