TransWikia.com

How create table variable with join in SQL

Stack Overflow Asked by Andrii Tkachenko on January 30, 2021

I have this query in SQL

SELECT  pr.Name                                                 AS ProfileName,
        pr.Code                                                 AS ProfileCode
FROM    dbo.CurrencyOperations co
JOIN dbo.CurrencyTransfers ct
    ON ct.OperId = co.id    
JOIN dbo.Accounts aco
    ON aco.id = ct.CreditId
    AND aco.Code LIKE '100%'
JOIN    dbo.cnbCashInRoad cr
    ON cr.CashInOperId = co.Id      
JOIN dbo.Labors lb
    ON lb.id = co.LaborId
JOIN dbo.Profiles pr
    ON pr.id = lb.ProfileId

How insert this query in TABLE variable?

2 Answers

It should be as simple as this

First create a temp table, which will act as your table variable

declare @tempTable table (name varchar(your field length), code varchar(your field length))

now insert your date into this temp table

insert into @tempTable (name, code)
SELECT pr.Name, -- no alias here
       pr.Code  -- no alias here
FROM   dbo.CurrencyOperations co
  JOIN dbo.CurrencyTransfers ct ON ct.OperId = co.id    
  JOIN dbo.Accounts aco ON aco.id = ct.CreditId
                       AND aco.Code LIKE '100%'
  JOIN dbo.cnbCashInRoad cr ON cr.CashInOperId = co.Id      
  JOIN dbo.Labors lb ON lb.id = co.LaborId
  JOIN dbo.Profiles pr ON pr.id = lb.ProfileId

now just use it like a normal table, for example

select * 
from   @tempTable

Answered by GuidoG on January 30, 2021

I would suggest just using a temporary table. That way, you can do:

select pr.name, pr.code
into #tablevar
from . . . ;

To use a table variable, you need to define it first and then insert:

declare @tablevar table (name varchar(255), code varchar(255));

insert into @tablevar (name, code)
    <your query here>;

Answered by Gordon Linoff on January 30, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP