Hi.
I'm parsing a json string into multiple columns and I am trying to insert it in a temp table.
I do not know the exact columns as they may vary from json to json so I need to insert them into the temp table like this:
declare @json nvarchar(max)
set @json = ( select responsebody FROM [APILog]
where AbsolutePath like '%complete%'
and id = 12)
select * Into
#tmpBigTable
select '1' as x,* from parseJSON(@json)
select * from #tmpBigTable
drop table #tmpBigTable
I get:
An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name.
If it helps, doing select '1' as x ,* from parseJSON(@json); will produce something like this:
x element_id sequenceNo parent_ID Object_ID NAME StringValue ValueType
1 1 1 1 NULL NULL L18 string
Do you not, also, get an "Must specify table to select from" message?
I would expect you to need
select * Into
#tmpBigTable
FROM
(
select '1' as x,* from parseJSON(@json)
) AS MyAliasName
if that isn't on then using Dynamic SQL may be necessary, and might need a double-hash temp table ##tmpBigTable (for scope to retain from inside-to-outside the dynamic SQL)
Hmm.
OK another problem.
When i use select * Into #tmpBigTable on a cursor it will say that there is already a table named #tmpBigTable
in the database.
Is there a way to union the information of the temp table through the cursor?
Thanks.
Not sure I understand the question, so perhaps either of these
SELECT *
INTO #MyTempTable
FROM
(
SELECT Col1, Col2, ...
FROM TableA
UNION ALL
SELECT ColX, ColY, ...
FROM TableB
) AS X
or
SELECT Col1, Col2, ...
-- Beware if any of the COLUMNS in the SELECT is an IDENTITY, as that will force that attribute on the #Temp column
INTO #MyTempTable
FROM TableA
INSERT INTO #MyTempTable
SELECT ColX, ColY, ...
FROM TableB
Hi.
The problem is that i use a cursor.
Here is what i try:
declare @json nvarchar(max)
declare @vv table (id int,responsebody nvarchar(max))
declare @x int
set @x=1
DECLARE @EID as INT;
DECLARE @EName as NVARCHAR(max);
DECLARE @BusinessCursor as CURSOR;
SET @BusinessCursor = CURSOR FOR
select top 3 id, responsebody FROM [APILog]
where AbsolutePath like '%complete%'
and id >= 52342;
OPEN @BusinessCursor;
FETCH NEXT FROM @BusinessCursor INTO @EID, @EName;
WHILE @@FETCH_STATUS = 0
BEGIN
set @json = (select @EName)
select * Into
#tmpBigTable
from (
select @EID as [ID],* from parseJSON(@json) ) AS MyAliasName
--select @EID,* from parseJSON(@json);
--PRINT cast(@EID as VARCHAR (50)) + ' ' + @EName;
FETCH NEXT FROM @BusinessCursor INTO @EID, @EName;
END
CLOSE @BusinessCursor;
DEALLOCATE @BusinessCursor;
select * from #tmpBigTable
drop table #tmpBigTable
I suggest you avoid them like the plaque - they almost invariable are slow and cause contention problems.
Pull all the data (that the Cursor would step through) into a #TempTable, include an IDENTITY column, and then LOOP through the #TempTable on the Identitiy Column
You can create an IDENTITY in a #TempTable using INTO like this:
SELECT IDENTITY(int, 1, 1) AS [MyID], Col1, Col2, ...
INTO #MyTempTable
FROM MyTable
...
DECLARE @intErrNo int, @intRowCount int
SELECT IDENTITY(int, 1, 1) AS [MyID], Col1, Col2, ...
INTO #MyTempTable
FROM MyTable
...
ORDER BY ColX, ColY -- Sequence to loop-process the rows
DECLARE @intLoop int = 1 -- Force first iteration
WHILE @intLoop >= 1
BEGIN
SELECT TOP 1
@Col1 = Col1,
@Col2 = Col2,
@intLoop = MyID+1
FROM #MyTempTable
WHERE [MyID] >= @intLoop
ORDER BY [MyID]
SELECT @intErrNo = @@ERROR, @intRowCount = @@ROWCOUNT
IF @intRowCount = 0
BEGIN
SELECT @intLoop = 0 -- Prevent any more iterations
GOTO LoopExit
END
-- ... perform loop tasks based on @Col1, @Col2 ...
LoopExit:
END -- Next loop iteration
-- Equivalent to FETCH NEXT FROM MyCursor INTO @Col1, @Col2, ...
-- Cleanup
DROP TABLE #MyTempTable -- Equivalent to CLOSE MyCursor DEALLOCATE MyCursor
??
I have absolutely no idea how to use what you wrote?
declare @json nvarchar(max)
DECLARE @intErrNo int, @intRowCount int
select IDENTITY(int, 1, 1) AS [MyID],(sessionid),(AbsolutePath),(query),(RequestHeaders),(RequestBody),(PartnerId),
(VistaMember),(ResponseHTTPStatusCode),(ResponseHeaders),(ResponseBody),(LogCreated) Into
#MyTempTable
FROM [APILog]
where AbsolutePath like '%complete%'
and id >= 52342
DECLARE @intLoop int = 1
WHILE @intLoop >= 1
BEGIN
SELECT TOP 1
@ResponseBody= ResponseBody,
@intLoop = MyID+1
FROM #MyTempTable
WHERE [MyID] >= @intLoop
ORDER BY [MyID]
SELECT @intErrNo = @@ERROR, @intRowCount = @@ROWCOUNT
IF @intRowCount = 0
BEGIN
SELECT @intLoop = 0 -- Prevent any more iterations
GOTO LoopExit
END
-- ... perform loop tasks based on @Col1, @Col2 ...
select * from parseJSON(@json)
LoopExit:
END -- Next loop iteration
-- Equivalent to FETCH NEXT FROM MyCursor INTO @Col1, @Col2, ...
-- Cleanup
DROP TABLE #MyTempTable -- Equivalent to CLOSE MyCursor DEALLOCATE MyCursor
If i leave it as is it will prompt:
Must declare the scalar variable "@ResponseBody".
If i remove the @ResponseBody it will loop and find 380 rows (instead of 3) and will return empty result in different result sets and also give arithmetic overflows.
Best I could do. It appears that the parsejson function has a declared identity in it , so I had to cut the Main table identity and add it afterwards.
Not sure that this is ideal but:
declare @json nvarchar(max)
DECLARE @intErrNo int, @intRowCount int
declare @x int
set @x=1
declare @xID int
select top 3 id as MYID,(sessionid),(AbsolutePath),(query),(RequestHeaders),(RequestBody),(PartnerId),
(Member),(ResponseHTTPStatusCode),(ResponseHeaders),(ResponseBody),(LogCreated) Into
#MyTempTable
FROM [APILog]
where AbsolutePath like '%complete%'
and id >= 52342
create table #tmpBigTableX(id int null)
DECLARE @intLoop int = 1
WHILE @intLoop >= 1
BEGIN
SELECT TOP 1
@json = ResponseBody,
@intLoop = MyID+1
FROM #MyTempTable
WHERE [MyID] >= @intLoop
ORDER BY [MyID]
SELECT @intErrNo = @@ERROR, @intRowCount = @@ROWCOUNT
IF @intRowCount = 0
BEGIN
SELECT @intLoop = 0 -- Prevent any more iterations
GOTO LoopExit
END
-- ... perform loop tasks based on @Col1, @Col2 ...
IF @x=1
Begin
insert into #tmpBigTableX
select @intLoop -1
set @xID = (select top 1 [ID] from #tmpBigTableX)
delete from #tmpBigTableX
select * Into
#tmpBigTable
from (
select @xID as [MYID], parent_ID,[Object_ID],[NAME],StringValue,ValueType from parseJSON(@json) ) AS MyAliasName;
set @x=2
END
ELSE
BEGIN
set @x=2
insert into #tmpBigTableX
select @intLoop -1
set @xID = (select top 1 [ID] from #tmpBigTableX)
delete from #tmpBigTableX
Insert into #tmpBigTable
select @xID as [MYID],parent_ID,[Object_ID],[NAME],StringValue,ValueType from parseJSON(@json) AS MyAliasName;
END
LoopExit:
END -- Next loop iteration
-- Equivalent to FETCH NEXT FROM MyCursor INTO @Col1, @Col2, ...
-- Cleanup
select * from #tmpBigTable
order by MYID
DROP TABLE #MyTempTable -- Equivalent to CLOSE MyCursor DEALLOCATE MyCursor
DROP TABLE #tmpBigTableX
DROP TABLE #tmpBigTable
Haven't looked closely at this, so I may be way off target:
If I do:
SELECT IdentityID,
Col1, Col2, ...
INTO #TEMP
FROM MyTable
where [IdentityID] has IDENTITY property, and if I don't want that column to have IDENTITY in the #TEMP table then I use CAST / CONVERT to "change" it:
SELECT CONVERT(int, IdentityID) AS [IdentityID],
Col1, Col2, ...
INTO #TEMP
FROM MyTable
and thus if you want a (new) IDENTITY in the #TEMP you can then do:
SELECT IDENTITY(int, 1, 1) AS [MyIdentity],
CONVERT(int, IdentityID) AS [IdentityID],
Col1, Col2, ...
INTO #TEMP
FROM MyTable
Hi.
I'm not sure how will convert an identity from a table that I do not know the exact columns, so I use "" .
Is there a way to do that with "" ?
The above examples I made assume that there are known columns but this may not be the case in some issues (note, I know this is not what I do in the above code, as I had to call all departments to agree on standard columns but a "*" may arise someday.
So let's say, in the above example, how would I skip the identity error?
Can I convert the first columns of unknown columns, knowing that it is an identity column?
So --insert into ( select (convert first column) * + 1 (so all the next columns but the first) from something -- )
I show some information_schema.columns examples but this is on the fly as it is a function that will create the table.
Thanks:
declare @json nvarchar(max)
declare @vv table (id int,responsebody nvarchar(max))
declare @x int
set @x=1
DECLARE @EID as INT;
DECLARE @EName as NVARCHAR(max);
DECLARE @BusinessCursor as CURSOR;
SET @BusinessCursor = CURSOR FOR
select top 3 id, responsebody FROM [APILog]
where AbsolutePath like '%complete%'
and id >= 52342;
OPEN @BusinessCursor;
FETCH NEXT FROM @BusinessCursor INTO @EID, @EName;
WHILE @@FETCH_STATUS = 0
BEGIN
set @json = (select @EName)
IF @x=1
Begin
select * Into
#tmpBigTable
from (
--------using * will include the identity (from " * "---------------------------------------
SELECT CONVERT(int, @EID) AS [ID],* from parseJSON(@json) ) AS MyAliasName
set @x=2
End
ELSE
-----------------identiry error here
Insert into #tmpBigTable
select @EID as [ID],* from parseJSON(@json) AS MyAliasName;
--select @EID,* from parseJSON(@json);
--PRINT cast(@EID as VARCHAR (50)) + ' ' + @EName;
FETCH NEXT FROM @BusinessCursor INTO @EID, @EName;
END
CLOSE @BusinessCursor;
DEALLOCATE @BusinessCursor;
select * from #tmpBigTable
drop table #tmpBigTable
P.S. This is not a current requirement so now worries but if there is a solution then good to know.
