Capturing value of identity column for use later?

[(Pete Cresswell)]

This doesn't work because the first INSERT is creating multiple
records for multiple projects. @@IDENTITY, then, contains the Identity
column value for the last tblWeekReportedLine record inserted.

Consequently, all the hours records are then associated with
that last value.

The source work table, #EstimateLines, is a pivoted representation
with a Begin/End date and some Hours for each of six periods - a line
per project that gets pushed up to the DB by some VB code.
Definition below the sample coding.

The "@WeekReportedID" value was successfully captured when
previous coding inserted six records into that table: one for
each date range (i.e. column in the UI screen)

Sounds like I'm approaching this wrong.

Suggestions on the right way to go about it?

---------------------
INSERT INTO tblWeekReportedLine
(
WeekReportedID,
RelativeLineNumber,
ProjectID
)
SELECT
@WeekReportedID1,
#EstimateLines.RelativeLineNumber,
#EstimateLines.ProjectID
FROM #EstimateLines;

SET @CurWeekReportedLineID = @@IDENTITY;

INSERT INTO tblHour
(
WeekReportedID,
WeekReportedLineID,
HoursDate,
Hours,
HoursTypeID,
HoursType,
TaxCodeID,
TaxCode
)
SELECT
@WeekReportedID1,
@CurWeekReportedLineID,
@BeginDate1,
Estimate1,
@DummyHoursTypeID,
@DummyHoursType,
@DummyTaxCodeID,
@DummyTaxCode
FROM #EstimateLines;
------------------------

The #Temp table create via VB:
------------------------

1030 .CommandText = "CREATE TABLE #EstimateLines " & _
" ( " & _
" PersonID int, " & _
" ProjectID int, " & _
" RelativeLineNumber int, " & _
" Available1 decimal(5,2) Default 0, Estimate1
decimal(5,2) Default 0, BeginDate1 DateTime, EndDate1 DateTime, " & _
" Available2 decimal(5,2) Default 0, Estimate2
decimal(5,2) Default 0, BeginDate2 DateTime, EndDate2 DateTime, " & _
" Available3 decimal(5,2) Default 0, Estimate3
decimal(5,2) Default 0, BeginDate3 DateTime, EndDate3 DateTime, " & _
" Available4 decimal(5,2) Default 0, Estimate4
decimal(5,2) Default 0, BeginDate4 DateTime, EndDate4 DateTime, " & _
" Available5 decimal(5,2) Default 0, Estimate5
decimal(5,2) Default 0, BeginDate5 DateTime, EndDate5 DateTime, " & _
" Available6 decimal(5,2) Default 0, Estimate6
decimal(5,2) Default 0, BeginDate6 DateTime, EndDate6 DateTime, " & _
" );"
------------------------

--
PeteCresswell

[Simon Hayes]

"(Pete Cresswell)" <x@y.z> wrote in message
news:dh********************************@4ax.com...

This doesn't work because the first INSERT is creating multiple
records for multiple projects. @@IDENTITY, then, contains the Identity
column value for the last tblWeekReportedLine record inserted.

Consequently, all the hours records are then associated with
that last value.

<snip>

Without full DDL (including keys) and knowing how you populate your
variables, this is a guess, but it may be along the right lines - you can
join onto the tblWeekReportedLine table to get the identity values:

INSERT INTO tblHour
(
WeekReportedID,
WeekReportedLineID,
HoursDate,
Hours,
HoursTypeID,
HoursType,
TaxCodeID,
TaxCode
)
SELECT
w.WeekReportedID,
w.IdentityColumn,
@BeginDate1,
e.Estimate1,
@DummyHoursTypeID,
@DummyHoursType,
@DummyTaxCodeID,
@DummyTaxCode
FROM #EstimateLines e
join tblWeekReportedLine w
on e.ProjectID = w.ProjectID and
e.RelativeLineNumber = w.RelativeLineNumber
WHERE w.WeekReportedID = @WeekReportedID1;

Simon

[(Pete Cresswell)]

RE/

this is a guess,

Pretty good guess!

I'm still an SQL novice, and haven't learned to stop thinking sequential
processing yet....

Thanks. I may make my Monday deadline yet.....
--
PeteCresswell