INSERT VALUES INTO TWO TABLES JOIN? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 VALUES INTO TWO TABLES JOIN?

Author

Topic

vicpal25
Starting Member

21 Posts

Posted - 2007-08-09 : 11:53:04

I got two tables "Meals" and "Calories". The tables have a relationship through the Meals primary key field "id".

I am witting a stored procedure that will insert values to both these tables. What is the most efficient way to go about this? Write two Insert statements? Is there such thing as an Insert with an Inner Join?

I have this right now as my INSERT statement:

INSERT meals (meal_title, dsc, meal_time, datestamp) VALUES (@v_title, @v_dsc, @v_mealtime, @v_datestamp)
INSERT calories (calories) VALUES (@v_calories)

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-08-09 : 12:36:12

I think what you want tt do is:
1. Use a transaction to wrap your inserts.
2. After you insert into Meals you can get the newly inserted ID my using SCOPE_IDENTITY(). (assuming it is an IDENTITY column)
3. Then use that ID to associate the new Meal with the Calories table.

For example (error handling omitted):

DECLARE @ID INT
BEGIN TRANSACTION
INSERT meals (meal_title, dsc, meal_time, datestamp) VALUES (@v_title, @v_dsc, @v_mealtime, @v_datestamp)
SET @ID = SCOPE_IDENTITY()
INSERT calories (mealsID, calories) VALUES (@ID, @v_calories)
COMMIT TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources