Temporary table with Primary Key from another table

Question

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

Please do not SHOUT when posting. Text in ALL CAPS is more difficult to read and understand, and will not get you an answer any faster. In addition, it's somewhat impolite to SHOUT at us. Thanks. — Ken White, Oct 04 '19 at 12:47
Possible duplicate of [How do you list the primary key of a SQL Server table?](https://stackoverflow.com/questions/95967/how-do-you-list-the-primary-key-of-a-sql-server-table) — GSerg, Oct 04 '19 at 12:49
Why do you need (or think you need) to enforce referencial Integrity on a temporary object? — Thom A, Oct 04 '19 at 12:49

MtwStark · Accepted Answer · 2019-10-04T16:38:54.070

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

Xaphas · Answer 2 · 2019-10-04T12:59:49.403

0

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

edited Oct 04 '19 at 12:59

answered Oct 04 '19 at 12:48

Xaphas

501
4
20

Please leave a comment when downvoting. – Xaphas Oct 04 '19 at 12:55
2

Not my downvote but I would assume that it was because at the time of the downvote your answer wasn't an answer. It was nothing more than a comment. – Sean Lange Oct 04 '19 at 13:04
@SeanLange I see. Thx for clarifying --> will look out for that in the future. – Xaphas Oct 04 '19 at 13:05

score 0 · Answer 3 · answered Oct 04 '19 at 13:23

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

Temporary table with Primary Key from another table

3 Answers3