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)
column4 (pk)

and the temp table will be like:

#table temp

...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) = '';

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.


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.


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

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

    sys.columns c
    sys.types t ON c.user_type_id = t.user_type_id
    sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
    sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
    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 

       IF @tablettype LIKE '%varchar%' 
       SET @typelengt = '(' + @typelengt + ')'
       SET @typelengt=''

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

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.