4

I have three tables to represent a single record in a one-to-many-to-many relationship. I would like to create a stored procedure in MS SQL Server to create the record all at once. I have seen examples on how to do this with just two tables with a table-valued parameter to populate the child table values, but can't find how to do the same with one more table deep.

  • Table 1 - request(req_id, requester, date)
  • Table 2 - reqitems(line_id, req_id, descript, qty)
  • Table 2 - itemcharge(charge_id, line_id, dept, percent)

So a request can have 1 or more items & each item can have 1 or more charges, adding up to 100%.

I appreciate any help you have. Thanks

Example to populate Tables 1 & 2

CREATE TYPE dbo.reqlineTable AS TABLE
(
    descript nvarchar(100),
    qty int
);
GO

CREATE PROCEDURE InsertRequest 
    @requester nvarchar(50), 
    @date datetime,
    @lines reqlineTable READONLY
AS
BEGIN

    SET NOCOUNT ON;

    DECLARE @req_id int;

    INSERT INTO dbo.request
           (requestor
           ,date)
     VALUES
           (@requester
           ,@date);

    SET @req_id = SCOPE_IDENTITY();

    INSERT INTO dbo.reqitems
    SELECT qty
        , descript
        , @req_id
    FROM @lines;

    SET NOCOUNT OFF;

END
GO
3
  • 1
    You can use XML or Table variable as your SP parameter. Aug 5 '16 at 14:48
  • 1
    @SlavaMurygin - I added some sample SQL to populate tables 1 & 2, but I'm not sure how to populate table 3 since there will be more than one line_id generated. How would I map the reqitems rows to the proper itemcharge rows? Aug 5 '16 at 23:41
  • Just send the data at itemcharge level, but make sure you also include the request_id. Then you have enough data to create all required records. May 11 '17 at 5:21
1

You can use the OUTPUT clause of the INSERT to capture the identity values that were generated like so:

DECLARE @output TABLE (
   line_id int,
   qty int,
   descript nvarchar(100),
   req_id int
);

INSERT INTO dbo.reqitems
OUTPUT INSERTED.line_id, INSERTED.qty, INSERTED.descript, INSERTED.req_id
  INTO @output
SELECT qty
    , descript
    , @req_id
FROM @lines;

Then you can use the @output table variable to perform the insert into your third table:

INSERT itemcharge
SELECT /* whatever values you need go here */
  FROM @output;

OUTPUT Clause (Transact-SQL)

0

Maybe something like this:

SELECT r.line_id, r.descript 
FROM reqitems as r 
INNER JOIN @lines as l
ON r.descript = l.descript
WHERE r.req_id = @req_id;

Then you link it to other values to insert into itemcharge
Only the way to link I see as by descript.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.