CODEWITHSUNDEEP
sqlsql-serverprimary-key
STiLeTT
STiLeTT

Reputation: 1073

Temp table: primary key with default name already exists

I have a following code in a stored procedure, running inside a cursor:

IF (OBJECT_ID('tempdb..#newSales','U') IS NOT NULL)
    DROP TABLE #newSales

CREATE TABLE #newSales
(
    AddressId INT NOT NULL,
    ItemId INT NOT NULL,
    [Date] DATE NOT NULL,
    Sale REAL NULL
)

/* some code here */

ALTER TABLE #newSales 
    ADD PRIMARY KEY CLUSTERED ([AddressId] ASC, [ItemId] ASC, [Date] ASC)

Periodically I get the following error (the name is different every time):

There is already an object named 'PK__#newSale__34CE1EAA297D3472' in the database.

I have checked similar questions, but most of them are with explicitly named constraints. I can't understand how this can happen, because in this case the PK's name is generated automatically and in theory it must be unique (even with multiple connections calling this procedure).

What can be the source of the error?

Upvotes: 2

Views: 1415

Answers (1)

LunarSage
LunarSage

Reputation: 285

You should avoid creating temporary tables where concurrency might be an issue and try using variable tables instead. Try the following:

DELCARE @newSales TABLE 
(
    AddressId INT NOT NULL,
    ItemId INT NOT NULL,
    [Date] DATE NOT NULL,
    Sale REAL NULL,
    PRIMARY KEY CLUSTERED ([AddressId] ASC, [ItemId] ASC, [Date] ASC)
)

INSERT INTO @newSales
SELECT *
FROM myOtherTables
...

Upvotes: 1

Related Questions