Select into #TempTable from @Variable results to Cartesian Product

Question

Insert a table variable into a TempTable with multiple columns (ID, Number, etc.)

One of the table variables is:

DECLARE @@gvTempTable TABLE (Number bigint, ID int) 

insert into  @@gvTempTable (Number) values ('21212321332332');
insert into  @@gvTempTable (Number) values ('100000000000');
insert into  @@gvTempTable (ID) values ('1');
insert into  @@gvTempTable (ID) values ('12');

select * into ##GlobalTempTable from @@gvTempTable;
select * from ##GlobalTempTable;

I need to display ID and Number as one row without NULL

This returns a kind of Cartesian Product:

[![Current vs Desired result set][1]][1]

you need to describe more in details, what is the logic here — Squirrel, Apr 01 '16 at 04:46
This worked for small number of records. But did not for large one. I got this message: Msg 10738, Level 15, State 1, Line 1006 The number of row value expressions in the INSERT statement exceeds the maximum allowed number of 1000 row values. — Data Engineer, Apr 01 '16 at 17:53
To bypass it I need to repeat the Insert Into @@gvTempTable(...) every thousand rows. — Data Engineer, Apr 01 '16 at 18:16

score 0 · Answer 1 · answered Apr 01 '16 at 04:56

this will gives you the desired result

select  Number = min(Number), ID = min(ID)
into    ##GlobalTempTable
from
(
    select *, rn = row_number() over (partition by case when Number IS NULL then 1 else 2 end order by ID) 
    from    @@gvTempTable
) d
group by rn

Select into #TempTable from @Variable results to Cartesian Product

1 Answers1