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?
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
)
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 ...
.