Insert into tables with relationships - DML (SELECT, INSERT, UPDATE, DELETE) Questions

Member of the LearnItFirst.com Video Training Network	\| LearnSqlServer.com \| SQL SSIS Training \| SQL Programming Tutorials \|

Welcome Guest

Search | New Posts | Members | Log In | Register

LearnSqlServer.com's SQL Server Forums » Transact-SQL Programming » DML (SELECT, INSERT, UPDATE, DELETE) Questions » Insert into tables with relationships

Insert into tables with relationships

Options

Previous Topic · Next Topic

dotnetmamma

Posted: Thursday, May 15, 2008 11:20:21 AM

Rank: Newbie

Joined: 5/15/2008
Posts: 1
Points: 3
Location: texas

Hi, I have two tables. One is called ContentRecord and it is one field, an identity field that is a primary key. I have another table that is has several fields but has a foreign key called contentFK. I have set up a foreign key relationship between the tables. There is a one to many relationship. I would like to know the best way to add a record to both tables. The way I am thinking to do it is to do the insert statement to add the record to the ContentRecord which will return the value of the primary key field. Then do a seperate insert statement to insert the record into the second table where I set the foreign key equal to the returned value from the first statement. Does that make sense? Is there a better way to do this?

Thanks,
Laura

Scott Whigham

Posted: Saturday, May 17, 2008 10:35:25 AM

Rank: Super Mod

Joined: 3/20/2006
Posts: 345
Points: 748
Location: Dallas, TX

You got it - that's the only way really. Most of us would probably have two stored procs: AddContentRecord and AddChildRecord (or whatever). It would look something like this:

Code:

CREATE PROC dbo.AddContentRecord (@ContentRecordId INT OUT)
AS
INSERT dbo.ContentRecord DEFAULT VALUES
SET @ContentRecordId = SCOPE_IDENTITY()
GO
CREATE PROC dbo.AddChildRecord (@ContentRecordId INT, @FieldA INT, @FieldB INT)
AS
INSERT ChildTable (ContentRecordId, FieldA, FieldB)
VALUES (@ContentRecordId, @FieldA, @FieldB)
Go

Now, to execute such would be like this:

Code:

DECLARE @id INT -- a local variable to hold the ContentRecordId
DECLARE @ReturnCode INT

BEGIN TRAN
BEGIN TRY
EXEC @ReturnCode = dbo.AddContentRecord @ContentRecordId=@Id OUT

IF @ReturnCode = 0
EXEC AddChildRecord @ContentRecordId=@Id, @FieldA = 4, @FieldB = 7

COMMIT TRAN
END TRY

BEGIN CATCH
ROLLBACK TRAN -- and do some more stuff
END CATCH

pk_bohra

Posted: Monday, May 19, 2008 1:31:34 AM

Rank: Newbie

Joined: 5/16/2008
Posts: 1
Points: 3
Location: Hyderabad

You can also try,

CREATE PROC dbo.AddMasternContentRecord ( @FieldA INT, @FieldB INT)
As
begin
BEGIN tran
INSERT dbo.ContentRecord DEFAULT values
INSERT dbo.ChildTable (ContentRecordId, FieldA, FieldB )
VALUES (@@identity,@FieldA, @FieldB )

IF @@Error <> 0
ROLLBACK TRAN -- and do some more stuff
ELSE
COMMIT TRAN
END

--Now, to execute such would be like this:
EXEC dbo.AddMasternContentRecord 270,300

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.