CODEWITHSUNDEEP
sql
user3670236
user3670236

Reputation: 133

SQL JSON Table Issue

I am importing data from an external JSON file, which is working properly, but I also wish to include a column which stores the current date / time, to store when the data was extracted.

CREATE PROCEDURE main.loadJSON
AS 

DECLARE @Details NVARCHAR(MAX)

SELECT @Details = 
    BulkColumn
    FROM OPENROWSET(BULK'C:\Users\Vally\Desktop\persondata.json',SINGLE_CLOB) JSON



    INSERT INTO main.jsontable (dateloaded,name,surname,email,age,balance,country,registered)
    VALUES(GETDATE(),
    (SELECT *
    FROM OPENJSON(@Details,'$.PersonDetails.Person')        
    WITH(
        [Name] NVARCHAR(50)         '$.first_name',
        [Surname] NVARCHAR(50)      '$.last_name',
        [Email] NVARCHAR(50)        '$.email',
        [Age] NVARCHAR(50)          '$.age',
        [Balance] NVARCHAR(50)      '$.balance',
        [Country] NVARCHAR(50)      '$.country',
        [Registered] NVARCHAR(50)   '$.registered'
        )
    )
)
GO

It is giving me 2 errors.

Line 27 Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Line 13 There are more columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.

What is the issue?

Upvotes: 0

Views: 33

Answers (1)

Randy
Randy

Reputation: 16677

refrain from using SELECT * instead specify all your columns explicitly

Upvotes: 1

Related Questions