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

Question

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

Answer 1

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.
  • Unfortunately you cannot use the (simpler) INSERT INTO x statement to insert into the first table because its OUTPUT statement only lets you output data that ended up in the x table but for some reason the MERGE statement's OUTPUT clause does let you OUTPUT additional data.
• 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;