Insert into 2 table without any problem

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Insert into 2 table without any problem

Author

Topic

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2009-01-28 : 05:59:39

Hi,

I've below table and value,

tblHdr
TrnxID | Msg
-----------------
1 | Hello1
2 | Hello2
3 | Hello3
4 | Hello4
.....
*TrnxID is a primary key with auto generate number

tblDetails
TrnxID | HdrTrnxID | Status
---------------------------------
1 | 1 | A
2 | 2 | A
3 | 3 | A
4 | 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 INT

SELECT @HdrTrnxID = MAX(TrnxID) FROM tblHdr

INSERT 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 this

INSERT INTO tblDetails(HdrTrnxID ,Status)
SELECT MAX(TrnxID),'A'
FROM tblHdr

Jai 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 then


DECLARE @TrnxID int

INSERT 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 like


INSERT INTO tblHdr (Msg)
SELECT Msg
FROM Source

INSERT INTO tblDetails(HdrTrnxID,Status)
SELECT h.TrnxID,s.Status
FROM Source s
INNER JOIN tblHdr h
ON h.Msg=s.Msg

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources