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 tblWeekReported Line 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 "@WeekReportedI D" 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 tblWeekReported Line
(
WeekReportedID,
RelativeLineNum ber,
ProjectID
)
SELECT
@WeekReportedID 1,
#EstimateLines. RelativeLineNum ber,
#EstimateLines. ProjectID
FROM #EstimateLines;

SET @CurWeekReporte dLineID = @@IDENTITY;

INSERT INTO tblHour
(
WeekReportedID,
WeekReportedLin eID,
HoursDate,
Hours,
HoursTypeID,
HoursType,
TaxCodeID,
TaxCode
)
SELECT
@WeekReportedID 1,
@CurWeekReporte dLineID,
@BeginDate1,
Estimate1,
@DummyHoursType ID,
@DummyHoursType ,
@DummyTaxCodeID ,
@DummyTaxCode
FROM #EstimateLines;
------------------------

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

1030 .CommandText = "CREATE TABLE #EstimateLines " & _
" ( " & _
" PersonID int, " & _
" ProjectID int, " & _
" RelativeLineNum ber 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.c om...

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 tblWeekReported Line 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 tblWeekReported Line table to get the identity values:

INSERT INTO tblHour
(
WeekReportedID,
WeekReportedLin eID,
HoursDate,
Hours,
HoursTypeID,
HoursType,
TaxCodeID,
TaxCode
)
SELECT
w.WeekReportedI D,
w.IdentityColum n,
@BeginDate1,
e.Estimate1,
@DummyHoursType ID,
@DummyHoursType ,
@DummyTaxCodeID ,
@DummyTaxCode
FROM #EstimateLines e
join tblWeekReported Line w
on e.ProjectID = w.ProjectID and
e.RelativeLineN umber = w.RelativeLineN umber
WHERE w.WeekReportedI D = @WeekReportedID 1;

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