joins

Question

joins

Mike Levan

SSChampion

Points: 14201
More actions
September 20, 2005 at 7:05 am

#91307

I have 3 tables as such.I want to insert data into these 3 tables.I have done insert and was able to get all the data but FK column(documentid) in tables vendor and transactions is NULL. Is there any way I can do insert with some inner joins? Below is the tables structure, pls look at it.
CREATE TABLE [Header] (
[Documentid] [int] IDENTITY (1, 1) NOT NULL ,
[recordtype] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[tcode] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[notes] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_Header] PRIMARY KEY CLUSTERED
(
[Documentid]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [vendor] (
[vendorid] [int] IDENTITY (1, 1) NOT NULL ,
[documentid] [int] NULL ,
[vendor] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[refdoc] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[intorder] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_vendor] PRIMARY KEY CLUSTERED
(
[vendorid]
) ON [PRIMARY] ,
CONSTRAINT [FK_vendor_Header] FOREIGN KEY
(
[documentid]
) REFERENCES [Header] (
[Documentid]
)
) ON [PRIMARY]
GO
CREATE TABLE [Transaction] (
[Transactionid] [int] IDENTITY (1, 1) NOT NULL ,
[documentid] [int] NULL ,
[fund] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[cost] [numeric](18, 0) NULL ,
CONSTRAINT [PK_Transaction] PRIMARY KEY CLUSTERED
(
[Transactionid]
) ON [PRIMARY] ,
CONSTRAINT [FK_Transaction_Header] FOREIGN KEY
(
[documentid]
) REFERENCES [Header] (
[Documentid]
)
) ON [PRIMARY]
GO

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 1

If this is to be one call from the client, you'd need to insert into Header, then get the identity value from the row using scope_identity() and use that in the other tables.

Mike Levan SSChampion Points: 14201 More actions · Answer 2

But I think scope_identity() wud return only the last Identity value from the parent table.

Roy Rogers SSCertifiable Points: 7712 More actions · Answer 3

CREATE PROCEDURE Header_Insert

(

@pRecordType CHAR(2),

@ptcode CHAR(2),

@pnotes VARCHAR(50),

@pDocumentID INT OUTPUT

)

AS

INSERT INTO Header (RecordType, tcode, notes) VALUES (@pRecordType, @ptcode, @pnotes)

SELECT @key = SCOPE_IDENTITY()

GO

CREATE PROCEDURE vendor_Insert

(

@pDocumentID INT,

@pVendor VARCHAR(50),

@prefdoc CHAR(5),

@pintorder CHAR(10)

)

AS

INSERT INTO Vendor (DocumentID, Vendor, refdoc, intorder) VALUES (@pDocumentID, @pVendor, @prefdoc, @pintorder)

GO

CREATE PROCEDURE Transaction_Insert

(

@pDocumentID INT,

@pfund CHAR(10),

@pcost NUMERIC(18, 0)

)

AS

INSERT INTO [Transaction] (DocumentID, fund, cost) VALUES (@pDocumentID, @pfund, @pcost)

GO

/* INSERT EXAMPLE FROM A FRONT END*/

DECLARE @DocumentID INT

EXEC Header_Insert 'AA', 'PP', 'Notes 1', @DocumentID OUTPUT

/* ASSIGN THE @DocumentID TO A VARIABLE */

EXEC vendor_Insert @DocumentID, 'Vendor Name', 'doc1', 'intordr'

EXEC Transaction_Insert @DocumentID, 'fund', 'cost'

Regards,
gova

Mike Levan SSChampion Points: 14201 More actions · Answer 4

What exactly I need is carry over all IDENTITY values from the latest transaction occured from the parent tables to child.Any pls help.

Merrill Aldrich SSCrazy Points: 2358 More actions · Answer 5

jp - you'll have to do the inserts one at a time, using the technique given above. If you need a single stored proc to insert multiple entries, then you can call these single inserts from another stored proc in a loop.