SQL definitely is not my area of expertise, so I need help with something.
Suppose I have 3 tables (I'll try and keep this is as simple as possible):
- User (with UserID). There are 13,000 records
- Status (StatusID as the PK). There are 10 records
- Workflow (WorkflowID as the PK)
Now suppose I have a fourth table:
- Notifications (UserID, StatusID, WorkflowID).
I need to be able to combine all the users, statuses and workflows into this one table so it creates all the possible combinations from the 3 tables listed (meaning there will be 260,000 new records via an insert). Using cursors is too time-consuming and I heard that using Joins will be more effective. Unfortunately, I'm not sure how to start it.
I believe what you are looking for is a Cartesian Product among all the tables. You might consider using a "Cross Join" like so: ... I put together a little test scenario based on your counts above (assuming by your expected insert count and the other counts, there should be 2 workflow rows) and it seemed to work fine. Hope this helps! Good luck!