Insert table from another table

Question

Insert table from another table

Seggerman-675349

Hall of Fame

Points: 3563
More actions
December 9, 2008 at 6:49 am

#197063

I have 2 tables which I am inserting using separate stored procedures:
RawHeader
HeadID INT
FromDate DATETIME
ToDate DATETIME et al
and RawDetail
DetailID INT
HeadID INT
Amount MONEY et al
after inserting the RawDetail I need to INSERT a DayDetail record
DayID INT
HeadID INT
Amount MONEY
now if there is no DayTable record I need to INSERT one
DayID INT
FromDate DATETIME
ToDate DATETIME et al
I wrote a function which takes the HeadID and looks up the DayID
then a stored procedure which INSERTs a Day record if there isn't one
but...
I am having difficulty passing the new DayID out of one stored procedure back to the calling stored procedure
do I - create a TABLE variable and INSERT it in there?
or
create a parameter as OUTPUT?
or
SET the new DayID as RETURN value?
this seems so simple but I spent 2 hours on it yesterday and ended up with a kluge to get it to work
thanks all

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

IMO, you should use an output parameter. This is the most efficient method. If you need help getting the output parameter to work, post some code and I'll take a look.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Seggerman-675349 Hall of Fame Points: 3563 More actions · Answer 2

Thanks

I used OUTPUT parameters a lot in Oracle so I think I should be able to get it to work

there's always BOL, too

🙂

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 3

Either an output parameter, or a return value, or you could return a one row, one column result.

In any case, I'd recommend OUTPUT parameters as well.

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 4

OUTPUT parameters will do the trick, but they'll only work for single row inserts. If you need to do any batch work, you'll want to look into the OUTPUT clause so that you can capture multiple ID's as they get created.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning