(newbie) insert into linked tables ?

[neverstill]

Hi-

I have 2 tables

[prod_Main]
ProdId int
Name varchar
ImgId tinyint

[Prod_Imgs]
ImgId tinyint
ProdId tinyint
PathTn varchar
PathBig varchar


OK, when I want to INSERT a new record with a sproc, I have a bit of a
challenge here.
prod_Main needs to know Prod_Imgs.ImgId and ProdImg.ProdId needs to know
Prod_Main.ProdId

I have a dependency cycle. Am I facing:
INSERT Prod_Main and get the ideentity
INSERT int Prod_Imgs get the identity
UPDATE Prod_Main with the identity from Prod_Imgs

or is there some magical solution that I don't know about. I'm just
learning all this stuff, so I might be missing something base here

Thanks
-Steve

 

[neverstill]

crap, I meant to post this to the msde group. Sorry.

If you care though, I just wrote this and it does the trick:




CREATE PROCEDURE proc_AddProduct
(
@PartNum nvarchar(8),
@Title nvarchar(120),
@Description nvarchar(4000),
@RetPrice smallmoney,
@DiscPrice smallmoney,

@ImgThumb nvarchar(60),
@ImgMain nvarchar(60),
@ImgHuge nvarchar(60),
@ImgSpec nvarchar(60)
)
AS

/*
* INSERT the Product info first, get the Id
* then INSERT the Images, then UPDATE the
* Products
*/
DECLARE
@Prod_Id tinyint,
@Img_Id tinyint


INSERT INTO Prod_Main
(PartNum, Title, [Description], RetPrice, DiscPrice, Availability, ImgId)
VALUES
(@PartNum, @Title, @Description, @RetPrice, @DiscPrice, 1, 1)

/* get the identity */
Set @Prod_Id = @@IDENTITY


/* insert the image data */
INSERT INTO Images
(ThumbN, Main, Huge, Spec)
VALUES
(@ImgThumb, @ImgMain, @ImgHuge, @ImgSpec)

/* get the identity */
Set @Img_Id = @@IDENTITY


/* now I need to insert the many to many */
INSERT INTO Prod_Images
(ProdId, ImgId)
VALUES
(@Prod_Id, @Img_Id)

/* update the products with ImgId */
UPDATE Prod_Main
SET ImgId = @Img_Id
WHERE ProdId = @Prod_Id
GO