Avatar of coldchillin
coldchillin
 asked on

TSQL - Nested Loop Inserts

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
Microsoft SQL Server 2008ColdFusion Language

Avatar of undefined
Last Comment
coldchillin

8/22/2022 - Mon
Guy Hengel [angelIII / a3]

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.
coldchillin

ASKER
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
ASKER CERTIFIED SOLUTION
kaminda

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
Guy Hengel [angelIII / a3]

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
coldchillin

ASKER
Thanks all (I will want to avoid duplicates)!

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

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck