My SQL statement with 2 INSERT commands is failing on the second INSERT

August 15, 2023

I have the following 2 INSERT statements:

-- Create a new database entry for the astronomicalObject in the GradObservers database
-- based on values of the given id
INSERT INTO astro.bodies (id, title, coordinates) 
    SELECT NEXT VALUE FOR bodies.nextid, title, 9291
    FROM astro.astro 
    WHERE id = 002811

-- Insert new id from table above into astro.sources
-- Insert other fields from existing bodyId 002811
INSERT INTO astro.sources(bodyId, baseValue, Url)
    (SELECT CONVERT(BIGINT, current_value) FROM sys.sequences WHERE schema_id = 71)
    SELECT baseValue, Url 
    FROM astro.sources 
    WHERE bodyId = 002811

I’m trying to do 2 INSERT commands where the 2nd one depends on the ID value of the first one.

The first INSERT uses a SEQUENCE in the database to get it’s ID.

Then the second INSERT needs to use that ID as well.

The first INSERT works, but I get an error from the 2nd statement:

The select list for the INSERT statement contains fewer items than the insert list. The number of SELECT values must match the number of INSERT columns.

Is there a way to make this work?

Thanks!

>Solution :

You have a single value being inserted, followed by a separate SELECT statement.

The correct syntax would have the nested SELECT inside the main SELECT. And for safety’s sake you should probably use the sequence name.

INSERT INTO astro.sources
  (bodyId, baseValue, Url)
SELECT
  (
    SELECT CONVERT(BIGINT, seq.current_value)
    FROM sys.sequences seq
    JOIN sys.schemas sch ON sch.schema_id = seq.schema_id
    WHERE sch.name = 'bodies'
      AND seq.name = 'nextid'
  )
  baseValue,
  Url 
FROM astro.sources 
WHERE bodyId = 002811;

However you are probably better off just storing it in a variable in the first place.

DECLARE @id bigint = NEXT VALUE FOR bodies.nextid;

INSERT INTO astro.bodies
  (id, title, coordinates) 
SELECT
  @id,
  title,
  9291
FROM astro.astro 
WHERE id = 2811;

INSERT INTO astro.sources
  (bodyId, baseValue, Url)
SELECT
  @id,
  baseValue,
  Url 
FROM astro.sources 
WHERE bodyId = 2811;

Side note: bodyId = 002811 doesn’t make much sense. 002811 is a number not a string, so why does it have leading zeroes.