Temporary table with Primary Key from another table

sql server create temp table with composite primary key
create primary key on temp table
create index on temp table in stored procedure
create temp table with index
drop index on temp table
sql server create table primary key
table scan on temp table
sql server select into temp table primary key

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


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 

Creating Primary Key on temporary table question, All,. Environment: Microsoft SQL Server Standard Edition (64-bit), 10.0.5520.0. I was doing a code review for another developer and came  I am trying to create a temporary table and join it with a standard table to create another temporary table. Example (this is only an example, the real tables and the select are more complicated): >> Creating the local temporary table. create local temporary column table #TEMPTABLE1 (DEPT_NO VARCHAR(50)); >> Filling the table with two rows


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

Indexing SQL Server Temporary Tables, In this article, we will explain SQL Server temp tables which are type of Defining PRIMARY KEY and UNIQUE KEY constraints during SQL temp table SQL temp table will be called, joined with other huge tables or if it will  To define a temporary table, we use the INTO statement after the SELECT statement. The name of a temporary table must start with a hash (#). Now, to see where this table exists; go to “Object Explorer -> Databases -> System Databases-> tempdb -> Temporary Tables”. You will see your temporary table name along with the identifier.


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 

SQL Server: temporary tables and constraint names, Local temporary tables are isolated in the scope of current session CREATE TABLE #temp_table ( id int NOT NULL PRIMARY KEY, name nvarchar(100) ). What do we do in the case of a composite primary key or other  In this example, we created a temporary table named #trek_products with two columns derived from the select list of the SELECT statement. The statement created the temporary table and populated data from the production.products table into the temporary table.


Creating Index on #temp tables, allow for the auto-generated columns (fields) to be created. Oracle introduced the global temporary table concept since version 8i. Unlike temporary tables from other database products such as MySQL and SQL Server, global temporary tables in Oracle are permanent database objects that store data on disk and visible to all sessions. However, the data stored in the global temporary table is private to the


CREATE TABLE (Transact-SQL), If the PRIMARY KEY or UNIQUE constraint creates a clustered index, the data In this case, a different FILESTREAM filegroup, partition scheme, or NULL can be specified. You can create local and global temporary tables. Use PRIMARY KEY (or just KEY) to make a column a primary key. A primary key is a special type of a unique key. There can be at most one primary key per table, and it is implicitly NOT NULL. Specifying a column as a unique key creates a unique index on that column. See the Index Definitions section below for more information. UNIQUE KEY Column


An Introduction to SQL Server Temporary Tables By Pracical , This tutorial covers SQL Server Temporary Tables and how to manipulate the However, if you open another connection and try the query above query, you will​  Primary key can only be created on physical table object. A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. object and lasts only for the duration of the query. So you cannot create PK on temporary result set.