CODEWITHSUNDEEP
sqlsql-servert-sql
Robertuzzo
Robertuzzo

Reputation: 169

Temporary table with Primary Key from another table

It's possible to create a temporary table with name of column where the name of the column are the primary key of another table. so for example...

table a
column1 (pk)
column2
column3
column4 (pk)
column5

and the temp table will be like:

#table temp
column1
column4

...so only just with the column of primary key from the table a how would be the code? Thank you

Upvotes: 0

Views: 407

Answers (3)

MtwStark
MtwStark

Reputation: 4058

You can use dynamic sql 

declare @sql nvarchar(1000) = '';
declare @col_list nvarchar(100) = '';

;with
n as (
    select tc.name, tc.column_id
    from sys.indexes i
    join sys.index_columns ic on i.object_id = ic.object_id and i.index_id = ic.index_id
    join sys.columns tc on i.object_id = tc.object_id and tc.column_id = ic.column_id
    where i.object_id = OBJECT_ID('table_a') 
    and i.is_primary_key = 1
)
select @col_list = substring((select ', ' + CAST(quotename(name) AS NVARCHAR(128)) [*] 
from n 
order by column_id 
for xml path('')), 2, 9999) 


set @sql = 'select ' + @col_list + ' into ##table_temp from table_a where 1=0'

print @sql;
exec sp_executesql @sql
select * from ##table_temp

Upvotes: 1

Esat Erkec
Esat Erkec

Reputation: 1744

The following code will help you but sp_executesql statment create the temp table in the other session for this reason you can use global temp table. 

DROP TABLE IF EXISTS ##Test

DECLARE @tbl_query as NVARCHAR(MAX) = 'CREATE TABLE ##Test ( '
DECLARE 
    @tablecol VARCHAR(300), 
    @tablettype   VARCHAR(300) ,
    @typelengt VARCHAR(300)

DECLARE cursor_product CURSOR
FOR sELECT 
    c.name 'Column Name',
    t.Name 'Data type' ,
        IIF(t.name = 'nvarchar', c.max_length / 2, c.max_length)

FROM    
    sys.columns c
INNER JOIN 
    sys.types t ON c.user_type_id = t.user_type_id
LEFT OUTER JOIN 
    sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
LEFT OUTER JOIN 
    sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
WHERE
    c.object_id = OBJECT_ID('your_table_name')
    and     ISNULL(i.is_primary_key, 0)=1

OPEN cursor_product;

FETCH NEXT FROM cursor_product INTO 
    @tablecol, 
    @tablettype,
    @typelengt

WHILE @@FETCH_STATUS = 0
    BEGIN
       IF @tablettype LIKE '%varchar%' 
       BEGIN
       SET @typelengt = '(' + @typelengt + ')'
       END
       ELSE
       BEGIN
       SET @typelengt=''
       END

        set @tbl_query = @tbl_query + @tablecol +  ' ' + @tablettype + @typelengt + ' , '
        FETCH NEXT FROM cursor_product INTO 
            @tablecol, 
    @tablettype,
    @typelengt
    END;

CLOSE cursor_product;

DEALLOCATE cursor_product;

SET @tbl_query = SUBSTRING(@tbl_query,1,LEN(@tbl_query)-1)
SET @tbl_query = @tbl_query + ' )'
PRINT @tbl_query
EXEC sp_executesql @tbl_query

Upvotes: 0

Xaphas
Xaphas

Reputation: 519

Yes, that is possible. The temp table doesn't care if the value you're feeding him is a primary key on your table of origin.

EDIT:

To answer your edited in question:

create table #temptable
(
    column1values datatype,
    column4values datatype
)

insert into #temptable
select column1, column4 from a

Upvotes: 0

Related Questions