I want to insert a newid() into a table. It's not supplied in the JSON object itself, so I need to pass it in somehow. I can't seem to do that...
我想在表中插入一个newid()。它不是在JSON对象本身中提供的,所以我需要以某种方式传递它。我似乎无法做到这一点......
declare @jsonString nvarchar(max),
--sample incoming data, JSON object
set @jsonString = '{
"PosTitle": "Tech",
"PosCode": "699887",
"FileName": "clickme.exe",
}'
I can parse the JSON string successfully, and insert it into the temp table:
我可以成功解析JSON字符串,并将其插入临时表:
--establish temp table
CREATE TABLE #tblDestination(
[id] [uniqueidentifier] default newsequentialid(),
[PosTitle] [varchar](80) NULL,
[PosCode] [varchar](5) NULL,
[FileName] [varchar](60) NULL,
)
--parse the JSON string
--and insert it into the temp table
insert into #tblDestination
select *
from openjson(@jsonString, '$')
with
(
newid(), --I need to insert a newid() into the [id column]
PosTitle varchar(80) '$.PosTitle',
PosCode varchar(5) '$.PosCode',
[FileName] varchar(60) '$.FileName',
)
I kinda can get it to work... Columns in the source must be lined up perfectly with the destination. The source doesn't have a newid(), so I need to build one and pass it in... but I can't figure out how to do that.
我有点可以让它工作......源中的列必须与目的地完美对齐。源没有newid(),所以我需要构建一个并传递它...但我无法弄清楚如何做到这一点。
My understanding is that with is part of a CTE.
我的理解是,这是CTE的一部分。
I was trying to avoid having to declare a var for each of the keys/values, and manually plucking each one out via select JSON_VALUE.
我试图避免为每个键/值声明一个var,并通过select JSON_VALUE手动拔出每个键。
1 个解决方案
#1
1
OpenJson is a table valued function, simply select NewId() and * from it. Also, Always specify the columns list when inserting data into a table:
OpenJson是一个表值函数,只需从中选择NewId()和*即可。此外,在将数据插入表时始终指定列列表:
insert into #tblDestination ([id], [PosTitle], [PosCode], [FileName])
select newid(), *
from openjson(@jsonString, '$')
with
(
PosTitle varchar(80) '$.PosTitle',
PosCode varchar(5) '$.PosCode',
[FileName] varchar(60) '$.FileName'
)
#1
1
OpenJson is a table valued function, simply select NewId() and * from it. Also, Always specify the columns list when inserting data into a table:
OpenJson是一个表值函数,只需从中选择NewId()和*即可。此外,在将数据插入表时始终指定列列表:
insert into #tblDestination ([id], [PosTitle], [PosCode], [FileName])
select newid(), *
from openjson(@jsonString, '$')
with
(
PosTitle varchar(80) '$.PosTitle',
PosCode varchar(5) '$.PosCode',
[FileName] varchar(60) '$.FileName'
)