insert identity and master/detail within t-sql transaction

12 Aug 2006 12:55 AM

miapjp

I've looked and looked and can't find the answer (except for ADO).
This has to have been asked before but I can't find the answer. I
need to insert a row, grab the PK (identity column) and then use that
for a child row. There's a foreign key between the two tables on that
PK. I've tried SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED but
still no go, I get a FK violation.
How can I get the master and child rows inserted within the
transaction? If I have to commit after the master insert it kind of
defeats the purpose....plus this is all within a bigger transaction.

TIA
Pat

12 Aug 2006 1:07 AM

Vern Rabe

Check out SCOPE_IDENTITY() in BOL. You don't have to commit after the master
insert.

HTH
Vern Rabe

Show quote

"mia***@chartermi.net" wrote:

> I've looked and looked and can't find the answer (except for ADO).
> This has to have been asked before but I can't find the answer. I
> need to insert a row, grab the PK (identity column) and then use that
> for a child row. There's a foreign key between the two tables on that
> PK. I've tried SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED but
> still no go, I get a FK violation.
> How can I get the master and child rows inserted within the
> transaction? If I have to commit after the master insert it kind of
> defeats the purpose....plus this is all within a bigger transaction.
>
> TIA
> Pat
>
>

12 Aug 2006 4:43 AM

miapjp

thanks Vern - although I had good error messages I neglected to see
that it was not the FK I thought it was but another. Too much coding
today/tonight/last few weeks, brain is seized up.
So all is well now - but thanks!

Pat

Vern Rabe wrote:
Show quote

> Check out SCOPE_IDENTITY() in BOL. You don't have to commit after the master
> insert.
>
> HTH
> Vern Rabe
>
> "mia***@chartermi.net" wrote:
>
> > I've looked and looked and can't find the answer (except for ADO).
> > This has to have been asked before but I can't find the answer. I
> > need to insert a row, grab the PK (identity column) and then use that
> > for a child row. There's a foreign key between the two tables on that
> > PK. I've tried SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED but
> > still no go, I get a FK violation.
> > How can I get the master and child rows inserted within the
> > transaction? If I have to commit after the master insert it kind of
> > defeats the purpose....plus this is all within a bigger transaction.
> >
> > TIA
> > Pat
> >
> >