How can I do an insert for two tables at the same time using SCOPE_IDENTITY()?

Question

I have 2 tables that I need to populate with 100 rows from a remote linked database.

The first table is easy, because I can just do a simple INSERT from a SELECT.

However, I also need to populate a second table, using the cameraId (which is automatically generated) from the first table.

But when I try to run the queries, I get this error:

Incorrect syntax near 'SCOPE_IDENTITY()'

Code:

INSERT INTO dbo.camera
(
    cameraId,     --AUTOMATICALLY GENERATED IDENTITY SEED VALUE
    productName,
    productDesc,
    productCode
)
SELECT CAMERA_ID, PRODUCT_NAME, PRODUCT_DESCRIPTION, PRODUCT_CODE
FROM [SQL_CANADA].[BASE].[dbo].PRODUCT
WHERE ORIGIN_ID = 19327761

WAITFOR DELAY '00:00:02';

INSERT INTO dbo.cameraLense
(
    lenseId,
    cameraId,      --SCOPE_IDENTITY() ??
    type,
    materialId,
    isCurrentYear,
    modelNumber
)
SELECT LENSE_ID, SCOPE_IDENTITY(), LENSE_TYPE, MATERIAL_ID, IS_CURRENT_YEAR, MODEL_NUMBER
FROM [SQL_CANADA].[BASE].[dbo].PRODUCT
WHERE ORIGIN_ID = 19327761

Is there anything that I could do to make this work?

Thanks!

Answer 1

SCOPE_IDENTITY() is not the magic you want it to be, and shouldn't really be used. Instead, use the OUTPUT clause with your insert.

/* Demo objects */
DROP TABLE IF EXISTS #Cameras; DROP TABLE IF EXISTS #CameraLenses;
CREATE TABLE #Cameras (CameraID BIGINT IDENTITY PRIMARY KEY, ProductName NVARCHAR(50), ProductDesc NVARCHAR(200), ProductCode NVARCHAR(6));
CREATE TABLE #CameraLenses (LensID BIGINT IDENTITY PRIMARY KEY, CameraID BIGINT FOREIGN KEY REFERENCES #Cameras (CameraID), LensType NVARCHAR(20), MaterialID BIGINT, IsCurrentYear BIT, ModelNumber NVARCHAR(10));

There's no point creating a FK on a temp table, expect for illustration, you can ignore the warning you get back during the insert.

DECLARE @Outputs TABLE (InsertedIdent BIGINT, InsertedProductCode NVARCHAR(6));
INSERT INTO #Cameras (ProductName, ProductDesc, ProductCode) 
OUTPUT INSERTED.CameraID, INSERTED.ProductCode INTO @Outputs
SELECT ProductName, ProductDesc, ProductCode
  FROM (
        SELECT 'UberCam' AS ProductName, 'Fine German craftsmanship' AS ProductDesc, 'UBER01' AS ProductCode
        UNION ALL
        SELECT 'AffreuseCam', 'Shoddy French knock-off', 'AFFR01'
        /* you can substitute this for a UDTT to pass in a table of cameras */
       ) a;

INSERT INTO #CameraLenses (CameraID, LensType, MaterialID, IsCurrentYear, ModelNumber) 
SELECT o.InsertedIdent, Type, MaterialID, IsCurrentYear, ModelNumber
  FROM (
        SELECT 'Digital' AS Type, 1 AS MaterialID, 1 AS IsCurrentYear, 'UBERLENS01' AS ModelNumber, 'UBER01' AS CameraProductCode
        UNION ALL
        SELECT 'Flash Bulb', 2, 0, 'AFFRLEBS01', 'AFFR01'
        UNION ALL
        SELECT 'Microscope', 2, 0, 'UBERLENS02', 'UBER01'
        /* you can substitute this for a UDTT to pass in a table of camera lenses */
       ) a
  INNER JOIN @Outputs o
    ON a.CameraProductCode = o.InsertedProductCode;

SELECT c.CameraID, c.ProductName, c.ProductDesc, c.ProductCode, cl.LensID, cl.CameraID, cl.LensType, cl.MaterialID, cl.IsCurrentYear, cl.ModelNumber
  FROM #Cameras c
    INNER JOIN #CameraLenses cl
      ON c.CameraID = cl.CameraID;

There's a few things going on here, so let's walk through it. First, we're declaring a table variable which we will use to hold pairs of the newly generated identity column, and something we already know about to link back to it.

Then we do an insert into the #Cameras table. Nestled in there between the INSERT and SELECT is the OUTPUT. All we're doing here is asking for the values from the pseudo table INSERTED` to be stuffed into it.

Next we do the insert into the #Lenses table. This time we have the data in our table variable, so we're able to join to it and collect the values we just inserted for #Cameras and reference them during our insert.

Finally, we do a quick select on the tables we just inserted into to review what happened.

CameraID	ProductName	ProductDesc	ProductCode	LensID	CameraID	LensType	MaterialID	IsCurrentYear	ModelNumber
1	UberCam	Fine German craftsmanship	UBER01	1	1	Digital	1	1	UBERLENS01
2	AffreuseCam	Shoddy French knock-off	AFFR01	2	2	Flash Bulb	2	0	AFFRLEBS01
1	UberCam	Fine German craftsmanship	UBER01	3	1	Microscope	2	0	UBERLENS02

Going further with this, you could also use User Defined Table Types to pass in the values you're going to insert, but they do come with a warning. Essentially you can create a UDTT to pass in a table. You define the table structure in the UDTT create, and then you can use it as if it were any other data type:

DECLARE @table NewInvoiceTable;
INSERT INTO @table (CustomerID, EmployeeID ...) VALUES
(1, 2 ...),(2, 2 ...);

You can only define them as parameters using the READONLY tag:

CREATE PROCEDURE dbo.InsertCustomers @NewCustomers NewCustomerTable READONLY...

You cannot drop a UDTT while something is referencing it, and you cannot ALTER TYPE at all. This makes it a pain if you need to change them.