Insert default value in insert from OPENJSON in SQL Server

Question

I'm creating a stored procedure to add in a table the data from a json. The table has an ID defined as uniqueidentifier and a CreatedAt defined as datetime.

If I try to run this script without ID and\or CreatedAt, I get this error

Column name or number of supplied values does not match table definition.

An example of the script is the following:

INSERT INTO [RequestsDetails]
SELECT *
  FROM OPENJSON(@json)
  WITH (
    RequestId        uniqueidentifier '$.RequestId',       
    QuoteId          uniqueidentifier '$.QuoteId',         
    SiteId           int              '$.SiteId',          
    SumBuilding      money            '$.SumBuilding'
  )

If I add the line using NEWID() for the ID and GETDATE() for the CreatedAt, I get the error

Incorrect syntax near NEWID

and this is an example of the script

INSERT INTO [RequestsDetails]
SELECT *
  FROM OPENJSON(@json)
  WITH (
    Id               uniqueidentifier NEWID(),
    RequestId        uniqueidentifier '$.RequestId',       
    QuoteId          uniqueidentifier '$.QuoteId',         
    SiteId           int              '$.SiteId',          
    SumBuilding      money            '$.SumBuilding',
    CreatedAt        datetime         GETDATE()
  )

How can I add those values that are not present in the json?

Update

If in the stored procedure, I have a parameter, how can I use it in the INSERT? For example

@RequestId uniqueidentifier

INSERT INTO [Sace].[RequestsDetails] (
    RequestId        
    -- omitted all the fields  
)
SELECT *
  FROM OPENJSON(@json)
  WITH (
    RequestId        uniqueidentifier @RequestId
    -- omitted all the fields
  )

Answer 1

The problem has nothing to do with OPENJSON; where the data is coming from, be that OPENJSON, a table, a VALUES clause, etc, etc, the error will occur. The problem is your INSERT INTO clause; you omitted the columns you want to INSERT into which means you are stating you want to INSERT a value into every column in that table (with the omission of IDENTITY columns).

The fix, therefore, is to fix your INSERT INTO clause; be explicit on the columns you want to INSERT into, which you should always be doing:

INSERT INTO dbo.[RequestsDetails] (RequestId,QuoteId,SiteId,SumBuilding)
SELECT RequestId,
       QuoteId,
       SiteId,
       SumBuilding
FROM OPENJSON(@json)
        WITH (RequestId uniqueidentifier, --We don't need '$.RequestId' as the name matches the column name
              QuoteId uniqueidentifier,
              SiteId int,
              SumBuilding money); --money is a poor choice for a data type; hopefully your table uses a decimal(16,4) or something

You can tell SQL Server to INSERT the default value, with the DEFAULT keyword, however, that is only supported with a VALUES table construct, not with a INSERT INTO... SELECT ....