Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
Delinda
Constraint Violating Yak Guru
315 Posts |
Posted - 2009-01-28 : 05:59:39
|
Hi,I've below table and value,tblHdrTrnxID | Msg-----------------1 | Hello12 | Hello23 | Hello34 | Hello4.....*TrnxID is a primary key with auto generate numbertblDetailsTrnxID | HdrTrnxID | Status---------------------------------1 | 1 | A2 | 2 | A3 | 3 | A4 | 4 | A*TrnxID is a primary key with auto generate number*HdrTrnxID is foreign key to relate to tblHdr(TrnxID)My question is how T-SQL looks like to 1. Insert value into tblHdr, and will select last row to obtain Last TrnxID from tblHdr.2. Once retrieve Last TrnxID from tblHdr, this value will be used to insert into tblDetails(HdrTrnxID,Status) values(**Last TrnxID from tblHdr**,'A')3. Me also want my T-SQL capable to handle multi-insert but who first will get a unique TrnxID from tblHdr (without same TrnxID from tblHdr) --- first in, first out.I am wonder, there's same TrnxID from tblHdr will be inserted into tblDetails. I always want my T-SQL successful and capable to prevent same TrnxID from tblHdr comes into tblMsgDetails(HdrTrnxID).Really need technical answer |
|
Jai Krishna
Constraint Violating Yak Guru
333 Posts |
Posted - 2009-01-28 : 06:08:30
|
Declare @HdrTrnxID INTSELECT @HdrTrnxID = MAX(TrnxID) FROM tblHdrINSERT INTO tblDetails(HdrTrnxID ,Status)VALUES(@HdrTrnxID,'A' )Jai Krishna |
|
|
Jai Krishna
Constraint Violating Yak Guru
333 Posts |
Posted - 2009-01-28 : 06:12:03
|
Or try thisINSERT INTO tblDetails(HdrTrnxID ,Status)SELECT MAX(TrnxID),'A'FROM tblHdrJai Krishna |
|
|
Delinda
Constraint Violating Yak Guru
315 Posts |
Posted - 2009-01-28 : 11:37:22
|
how about run T-SQL for insert into tblHdr together with insert into tblDetails? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-28 : 12:42:46
|
if you does only a single insert at a time in master thenDECLARE @TrnxID intINSERT INTO tblHdr (Msg)VALUES ('Hello1')SET @TrnxID=SCOPE_IDENTITY()INSERT INTO tblDetails (HdrTrnxID,Status)VALUES (@TrnxID,'A').... |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-28 : 12:52:24
|
and if you does some batch inserts then you can use something likeINSERT INTO tblHdr (Msg)SELECT MsgFROM SourceINSERT INTO tblDetails(HdrTrnxID,Status)SELECT h.TrnxID,s.StatusFROM Source sINNER JOIN tblHdr hON h.Msg=s.Msg |
|
|
|
|
|
|
|