CODEWITHSUNDEEP
sql-servert-sqlprimary-keytemp-tables
EvgenOrel
EvgenOrel

Reputation: 139

SQL Server temporary tables with a key in different sessions

In a stored procedure, I use a temporary table with a primary key.

CREATE TABLE #tmpTable
(
    [RowId] [bigint] IDENTITY(1,1) NOT NULL,
    [Id] [numeric](10, 0) NOT NULL
)

ALTER TABLE #tmpTable 
    ADD CONSTRAINT PK_NamePK PRIMARY KEY CLUSTERED (RowId);

The procedure works, but if I run the same procedure in another session I get an error

'PK_NamePK already exists'

How to use keys or indexes on temporary tables so that they are visible only in their scope?

Upvotes: 1

Views: 73

Answers (1)

Alfin E. R.
Alfin E. R.

Reputation: 928

   CREATE TABLE #tmpTable
   (
        [RowId] [bigint] IDENTITY(1,1) NOT NULL PRIMARY KEY,
        [Id] [numeric](10, 0) NOT NULL
    )

There is no need to add alter table, you can define by table creation. Hope this work, I have never tried to add primary key on temptable before.

Upvotes: 2

Related Questions