sqlsql-serveropen-json

Insert default value in insert from OPENJSON in SQL Server


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

enter image description here

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
  )

Solution

  • 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 ....