1,105,384 Community Members

Using table join instead of cursor

Member Avatar
Junior Poster in Training
60 posts since Aug 2009
Reputation Points: 0 [?]
Q&As Helped to Solve: 4 [?]
Skill Endorsements: 0 [?]

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

Member Avatar
Veteran Poster
1,056 posts since Jun 2011
Reputation Points: 239 [?]
Q&As Helped to Solve: 212 [?]
Skill Endorsements: 17 [?]

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.

Member Avatar
Practically a Posting Shark
895 posts since Feb 2011
Reputation Points: 442 [?]
Q&As Helped to Solve: 163 [?]
Skill Endorsements: 16 [?]

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!

This article has been dead for over three months: Start a new discussion instead
Start New Discussion
Tags Related to this Article