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
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
if it times out, it is likely missing indexes.
you could do it at once:
still, you might required indexes to make sure the query runs fast.
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
still, you might required indexes to make sure the query runs fast.
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
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
INSERT INTO ProgramCustomer (ProgramID, CustomerID)
SELECT p.ProgramID, c.CustomerID
FROM Program p
INNER JOIN Customer c ON c.GroupCode = p.GroupCode
I see.
yes, I think kaminda's code should be what you need.
unless you want to avoid to insert duplicates (next step)
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 )
Premium Content
You need an Expert Office subscription to comment.Start Free Trial