SQL Insert Using a user defined table into two table with the second joining onto the first

Nikita 0
Viewed 39

I have a type table that contains 3 values

  1. An ID
  2. A String value
  3. And a int

The first table that i insert into takes only the string value

The second table is where my question lies I need to insert into it the ID in the type table, the int and finally the corolating ID from the first table

So Table one would look like this after the import

Table1ID String
Table1ID1 String1
Table1ID2 String2

And table 2 after the import

Table2ID IDFromType Table2ID IntFromType
Table2ID1 IDFromType1 Table1ID1 IntFromType1
Table2ID2 IDFromType2 Table1ID2 IntFromType2

enter image description here

enter image description here

sql-server sql
1 Answers

I assume you have something like this:

CREATE TYPE dbo.PuppyType AS TABLE (
    PuppyId    int          NOT NULL PRIMARY KEY,
    Name       nvarchar(50) NOT NULL,
    BarkVolume int          NOT NULL
);

GO

CREATE TABLE dbo.Doggos (
    DoggoId       int          NOT NULL IDENTITY PRIMARY KEY,
    SourcePuppyId int              NULL,
    Name          nvarchar(50) NOT NULL
);

GO

CREATE TABLE dbo.BarkMeasurements (
    MeasurementId int NOT NULL IDENTITY PRIMARY KEY,
    DoggoId       int NOT NULL,
    BarkVolume    int NOT NULL,

    CONSTRAINT FK_Barks_to_Doggos FOREIGN KEY ( DoggoId ) REFERENCES dbo.Doggos ( DoggoId )
);

Your PROCEDURE should look like this (there's a lot of boilerplate for transactions and error-handling...):

  • Use a TRY block with BEGIN TRANSACTION + COMMIT TRANSACTION to ensure the operation is atomic: so if it fails then everything will be rolled-back and you won't have incomplete data in your database.
  • Use the OUTPUT clause of DML (redirected to a local table-variable) to capture newly generated IDENTITY values and reuse them for mapping and inserting into additional tables.
  • So use MERGE INTO with an always-false match condition (ON 1 = 0) to force an INSERT-only MERGE and OUTPUT the inserted data to a table-variable @dboPuppiesInsertOutput.
  • Then for the second table you can use a simpler INSERT INTO x ( y, z ) SELECT y, z FROM a INNER JOIN @dboPuppiesInsertOutput statement.
    • You can add an OUTPUT clause to this too to return that inserted data to the SQL Server client if you like.

Like so:

CREATE PROCEDURE dbo.InsertPuppyData
    @puppies dbo.PuppyType READONLY
AS

SET NOCOUNT ON;
SET XACT_ABORT ON;

BEGIN TRY;

    BEGIN TRANSACTION insertPuppiesTxn;

    --

    DECLARE @dboPuppiesInsertOutput TABLE (
        PuppyId int NOT NULL PRIMARY KEY,
        DoggoId int NOT NULL
    );

    -- Need to use MERGE because INSERT doesn't let you OUTPUT extra data.
    -- Also need HOLDLOCK: https://dba.stackexchange.com/questions/187776/merge-deadlocking-prevention

    MERGE INTO dbo.Doggos WITH (HOLDLOCK) AS tgt
    USING @puppies AS src ON 1 = 0
    WHEN NOT MATCHED BY TARGET THEN
        INSERT ( SourcePuppyId, "Name" )
        VALUES ( src.PuppyId, src."Name" )
    OUTPUT
        src.PuppyId,
        inserted.DoggoId
        INTO @dboPuppiesInsertOutput;

    -- We can use INSERT here as we don't need MERGE's more powerful OUTPUT clause:
    INSERT INTO dbo.BarkMeasurements ( DoggoId, BarkVolume )
    SELECT
        m.DoggoId,
        p.BarkVolume
    FROM
        @puppies AS p
        INNER JOIN @dboPuppiesInsertOutput AS m ON p.PuppyId = m.PuppyId;

    --

    COMMIT TRANSACTION insertPuppiesTxn;

    RETURN 0;

END TRY
BEGIN CATCH

    IF @@TRANCOUNT >= 1 ROLLBACK TRANSACTION insertPuppiesTxn;

    THROW;

END CATCH;
usertqF0
Related