LearnItFirst User Forum

Welcome Guest

Search | New Posts | Members | Log In | Register

SQL Server Forum - LearnItFirst.com » SQL Server Database Administration » General SQL Database Question & Answer » many-to-many with identity's and inserting with sp.

many-to-many with identity's and inserting with sp.

Options

Previous Topic · Next Topic

sqladmin

Posted: Sunday, July 26, 2009 12:56:04 PM

Rank: Fledgling

Joined: 9/10/2007
Posts: 11
Points: 33
Where do you live?: us.fl

how does one write an insert statement to tables that are part of a many-to-many relation?

lets say i have 3 tables. table1, table2, & junction ( all tables linked via identity columns )

now i want to write a dynamic stored procedure to insert/update to one or all the tables. how is
this done?

any help with this is greatly appreciated.

thanks in advance.

fyi; below is the script to produce the many-to-many tables

-- Create 3 tables
-- Table1, Table2, Junction

create table table1
(
wetid int not null identity (1,1) primary key
, col1 varchar(10)
, col2 varchar(10)
)
go

create table table2
(
dryid int not null identity (1,1) primary key
, col1 varchar(10)
, col2 varchar(10)
)
go

create table junction
(
table1wetid int references table1 (wetid)
on update cascade
on delete cascade
, table2dryid int references table2 (dryid)
on update cascade
on delete cascade
, primary key (table1wetid, table2dryid)
, cola varchar(10)
, colb varchar(10)
, colc varchar(10)
)

sqladmin

Posted: Sunday, July 26, 2009 1:28:41 PM

Rank: Fledgling

Joined: 9/10/2007
Posts: 11
Points: 33
Where do you live?: us.fl

oh by the way... these inserts are not mass inserts or any thing... just scaler values.

one row at a time across multiple tables is all.

i'm just not that privy to all the tsql particulars; and need some examples.

the main goal is 'somehow' pass in values from a web form, and have the sp perform
the insert or update what have you.

thanks again to anybody who can help.

sqladmin

Posted: Sunday, July 26, 2009 3:52:19 PM

Rank: Fledgling

Joined: 9/10/2007
Posts: 11
Points: 33
Where do you live?: us.fl

if there is a video on this subject... would gladly take a look at it. the videos at learnsqlserver.com are outstanding!

Scott Whigham

Posted: Monday, July 27, 2009 8:58:34 AM

Rank: Super Mod

Joined: 3/20/2006
Posts: 476
Points: 1,053
Where do you live?: Dallas, TX

All modification statements are single-table only; you cannot write an INSERT, UPDATE, or DELETE statement that modifies more than one table. You are going to have to write multiple statements.

This is very, very common. Usually we have a parent record followed by children. Let's take a real-world example of an ecommerce website purchase. For this example, the user must have an account to complete the purchase. Here's what has to happen:

Create the account (i.e. add a row to dbo.WebAccount table)
Create a shopping cart (i.e. add a row to dbo.ShoppingCart table)
Add shopping cart items (i.e. add rows to dbo.ShoppingCartItem)

There are really two parents here: dbo.WebAccount and dbo.ShoppingCart. The dbo.ShoppingCartItem table is a child of dbo.ShoppingCart (which is a child of dbo.WebAccount).

There are no shortcuts: you must create separate INSERTs for each. Usually you would have granular/modular stored procedures to handle each item:

dbo.AddWebAccount
dbo.CreateShoppingCart
dbo.AddShoppingCartItem

In your front end/caller, you would call each one individually.

I have seen people try to create one massive stored proc to handle all of this but I advise you to not do that! It gets messy, you end up with tons of (often optional) parameters, and it is just not re-usable.

Users browsing this topic

Guest

Forum Jump

You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.