Using table join instead of cursor

[f_atencia]

Hi all,

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.

Help is greatly appreciated.

Thanks in advanced

[adam_k]

Read here: http://msdn.microsoft.com/en-us/library/ms187518.aspx
You should focus on full outer join and before inserting, you should try with just selecting them. You might need to filter the results prior to your insert.

[BitBlt]

I believe what you are looking for is a Cartesian Product among all the tables. You might consider using a "Cross Join" like so:

insert into dbo.notifications
(userid, statusid, workflowid)
select userid, statusid, workflowid
from dbo.users
cross join dbo.status
cross join dbo.workflow

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!