INSERT among 3 tables and 1 is Junction Table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

INSERT among 3 tables and 1 is Junction Table

Author

Topic

downseven
Starting Member

1 Post

Posted - 2007-09-22 : 09:31:09

Looking for some help here, so thanks for any input. I'm a painfully new newbie to SQL scripting.

Situation: I have a simple database to handle an organization's events. Those events are categorized and may have more than one category assigned to each event. I need a maintenance Web Form to update their events.

Set Up (so far): I have a CATEGORIES table. It has an auto incrementing UID and a Category name field. This table will be updated so infrequently, I plan to update it manually (no need for a maintenance Web Form). Next is the EVENTS table. It also has an auto incrementing UID along with several fields (Title, Location, DateTime, etc.). The junction table is named jEVENTSCATEGORIES. It has its own auto incrementing UID along with 2 fields named for the primary keys (UIDs) in the other 2 tables (EventsID and CategoryID).

Goal: On the Web Form, I have a CheckBoxList control that's populated by the CATEGORIES table. One or more categories can be checked for each event. I have a FormView control that allows Edits and Inserts.

Need: I need to know the INSERT statement(s) required to insert a new record in the EVENTS table and then to update one or more rows in the junction table (jEVENTSCATEGORIES).

My Assumptions: I know how to create SELECTs and INSERTs and whatnot, but I'm not certain how to create a second INSERT statement that is based on a variable (or output) from a previous action. So any help would be MUCH appreciated. Thanks for your time!

Kristen
Test

22859 Posts

Posted - 2007-09-23 : 02:17:57

Create a comma delimited list of Categories selected for an Event - you will get an Array of results for the checkboxes from the web form, so its pretty easy to express that as a delimited list.

Then using SQL Split the comma delimited list and join the result against the Event, resulting in one row per Event/Category combination.

Then use that to adjust the EventCategory table.

So something like this:


INSERT INTO jEVENTSCATEGORIES(MyEventColumn, MyCategoryColumn)
SELECT 1234,      -- The event number
       SplitValue -- The value from the Category list delivered by the Split Function
FROM MySplitFunction('1,2,3,4,5') -- The category list

If you are updating an existing event you might pre-delete the old category choices:


BEGIN TRANSACTION

DECLARE @intError int
SET @intError = 0

DELETE D
FROM jEVENTSCATEGORIES AS D
WHERE MyEventColumn = 1234
IF @@ERROR = 0 SET @intError = 1

INSERT INTO jEVENTSCATEGORIES ...
SELECT ...
FROM MySplitFunction(...)
IF @@ERROR = 0 SET @intError = 2

IF @intError = 0
BEGIN
    COMMIT
END
ELSE
BEGIN
    ROLLBACK
END

See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Best%20split%20functions

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources