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

Question

I need to insert multiple Table variables into one temp table.

One of the table variables is:

DECLARE @@TempTable_Number TABLE (Number bigint) 

insert into  @@TempTable_Number (Number) values ('000000000000');
insert into  @@TempTable_Number (Number) values ('100000000000');

This works for inserting just one table variable

select * into ##GlobalTempTable_1 from @@TempTable_Number

I have a couple more table variables like

DECLARE @@TempTable_ID TABLE (Number int) 

insert into  @@TempTable_ID (ID) values ('1');
insert into  @@TempTable_ID (ID) values ('12');

etc...

I tried this to insert data from multiple table variables into one TempTable:

Select * into  ####GlobalTempTable_1 From @@TempTable_ID,  @@TempTable_Number;

The query goes to a continuous loop...

EDIT:

One of the table variables is:

DECLARE @@TempTable_Number 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;

This returns a kind of a cartesian product

Your example is odd. First you store bigint/int but provide strings. Not big deal (implicit conversion will handle it). Second your @@TempTable_ID has invalid INSERT INTO statement (column names Number <>ID). — Lukasz Szozda
– Lukasz Szozda, Commented Mar 31, 2016 at 23:29
It is a typo, I meat to use ID in the second column. I updated my question, thanks again. — Data Engineer
– Data Engineer, Commented Apr 1, 2016 at 3:15
Please do not edit question,like you did, becaue you invalidate existing answers. If you want to add clarification mark it clearly. Second your edit is a new question and should be asked separately. — Lukasz Szozda
– Lukasz Szozda, Commented Apr 1, 2016 at 10:32

Lukasz Szozda · Accepted Answer · 2016-03-31 23:28:24Z

3

Use UNION ALL:

SELECT ID
INTO  ##GlobalTempTable_1 
FROM @@TempTable_ID
UNION ALL
SELECT Number
FROM @@TempTable_Number;

LiveDemo

Select * into  ####GlobalTempTable_1 From @@TempTable_ID,  @@TempTable_Number;
The query goes to a continuous loop...

It is probably not loop but very long query. Keep in mind that you do Cartesian product.

So your query is the same as:

SELECT * 
INTO  ##GlobalTempTable_1 
FROM @@TempTable_ID
CROSS JOIN  @@TempTable_Number;

And the result is NxM records where N is number of records in first table and M in the second.

edited Mar 31, 2016 at 23:28

answered Mar 31, 2016 at 23:21

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Data Engineer Over a year ago

Thanks for the answer. But It produces a different from what I'm expecting result. I modified my previous query I got really close, but not 100%

Data Engineer Over a year ago

I did the following: DECLARE @@TempTable TABLE (Number bigint, ID int) insert into @@gvTempTable (Number) values ('00000000000000'); insert into @@gvTempTable (Number) values ('00000000000012'); insert into @@gvTempTable (ID) values ('1'); insert into @@gvTempTable (ID) values ('12'); Select * into ##TempTable From @@gvTempTable

Data Engineer Over a year ago

The result set still returns Cartesian Product in two columns, but I need it to return ID and NUMBER as one row not return NULLs for all IDs and then for all Numbers separately.

StackUser · Accepted Answer · 2016-04-01 07:46:59Z

Try like this,

DECLARE @TempTable TABLE (
    ID INT
    ,Number BIGINT
    )

INSERT INTO @TempTable (Number)
VALUES ('21212321332332');

INSERT INTO @TempTable (Number)
VALUES ('100000000000');

INSERT INTO @TempTable (ID)
VALUES ('1');

INSERT INTO @TempTable (ID)
VALUES ('12');

--select * into #GlobalTempTable from @@gvTempTable;
--select * from ##GlobalTempTable;
SELECT *
FROM @TempTable

SELECT A.ID
    ,B.Number
FROM (
    SELECT ID
        ,ROW_NUMBER() OVER (
            ORDER BY ID
            ) TempId
    FROM @TempTable
    WHERE id IS NOT NULL
    ) A
INNER JOIN (
    SELECT number
        ,ROW_NUMBER() OVER (
            ORDER BY id
            ) TempId
    FROM @TempTable
    WHERE number IS NOT NULL
    ) B ON A.TempId = B.TempId

Collectives™ on Stack Overflow

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

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related