INSERT without using cursors
-
I have 4 Tables like so
Table Trips
TripID
TripDate
Table Customers
CustID
TripID
Name
Table Items
ItemID
TripID
Quantiity
Table 4 ItemCustomers
ItemCustID
ItemID
CustID
Each Trip has multiple Customers who buy multiple Items, but not necessarily the same Items. I’d like to INSERT rows into the ItemCustomers Table via a SP, with one of the methods of doing so being to assign all Customers to all Items. I know I could do this using CURSORS but want to know of a better way, does anyone know of one?
Thanks,
Brian
-
Use a join to get a cartesian of all
insert into ...
select b.itemid, a.custid
from customers a
join items b on 1=1
Add a filter for one cust or add the other table if the relationship is less direct.
Do the insert to a temp table and use identity function if you need to create a sequential number for each row, then insert from the temp table to the real table.
AND if you ever think you have to use a cursor for anything, you're likely wrong. Cursors are evil.
-
Thanks John, worked perfectly!
-
would this be the "correct" join to use??
from customers a
cross join items b
-
"cross join" Doh!
I confess the stupid modern join syntax is a second language to those like me that have been doing SQL since before the alphabet was invented.
-
Lol, there's nothing wrong with your solution... it just looks funny to me being less than 100 yo
.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply