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
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
Last Comment
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
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
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.
SOLUTION
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.
ASKER
Thanks all (I will want to avoid duplicates)!
That seems like what I'm looking for, thanks!
That seems like what I'm looking for, thanks!
you could do it at once:
Open in new window
still, you might required indexes to make sure the query runs fast.