Capturing value of identity column for use later?

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"(Pete Cresswell)" <x@.y.z> wrote in message
news:dhmtov0kgau16c4opg7ktc2d69agtieh0t@.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|||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