CODEWITHSUNDEEP
sqlsql-serverglobal-variablestemp-tables
Data Engineer
Data Engineer

Reputation: 827

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

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

Result sets

Upvotes: 3

Views: 3992

Answers (2)

StackUser
StackUser

Reputation: 5398

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

Upvotes: 0

Lukasz Szozda
Lukasz Szozda

Reputation: 176189

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.

Upvotes: 3

Related Questions