SQLTeam.com | Weblogs | Forums

Insert into an unknown table using select


#1

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


#2

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)


#3

Hi no i do not get must specify table.

With your code i get Incorrect syntax near ')'. that show the last parenthesis (in bold)


#4

Sorry, needs an Alias for the sub-select. I've edited the original


#5

Ah good that works now.
Will try to implement id to a cursor I am using and see if I have any more issues.
thanks.


#6

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.


#7

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

#8

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

#9

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

#10

Hi will try it later on.
i'm not sure about the identity loop though?


#11
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

#12

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


#13

Looks like you could change @ResponseBody to @json

...
SELECT TOP 1
    @json = ResponseBody,
        @intLoop = MyID+1
 ...

I don't have time to do the work for you, sorry.


#14

Hey, no problem.
Thanks for the help so far :slight_smile:


Fix performance of database 60GB with json strings
#15

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

#16

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 

#17

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.