CODEWITHSUNDEEP
sqlsql-server
dhs4402
dhs4402

Reputation: 143

Primary key in a temporary table in SQL server causing ' There is already an object' error

I'm tring to upload data from python to a temp table in database and delete it after use. However, everytime I rerun it, it always gives a error

There is already an object named 'PK_temp' in the database.

It seems very strange to me as I think by dropping the temp table, the primary key I created for this table will also be dropped automatically.

To be more specific, I'm doing this

Step 1: drop the temp table if there is any

run_sql("""IF OBJECT_ID (N'tempdb..#temp') IS NOT NULL drop table #temp""", engine)

Step 2: create the temp table with a primary key and upload data by passing it as xml string

query = """

set nocount on;

declare @temp varchar(max);
select @temp = '{xml_str}';

-- CREATE TEMP TABLES

CREATE TABLE #temp(
id int not null,
CONSTRAINT PK_temp PRIMARY KEY (id asc),

);

-- EXTRACT XML INTO TEMP TABLES
declare @xml xml
declare @hdoc int;

select @xml = @temp 
exec sp_xml_preparedocument @hdoc OUTPUT, @xml

insert into #temp (id)
select  id
from    OPENXML(@hdoc, '/data/row', 2)
with (id int)
exec sp_xml_removedocument @hdoc;
""".format(xml_str = id_xml_str)

run_sql(query, engine)

To be more clear, my run_sql looks like this

def run_sql(sql, engine)
    session = Session(engine)
    try:
        session.execute(clause=sql)
        session.commit()
    except Exception as e:
        session.rollback()
        raise e
    finally:
        session.close()
        return

and id_xml_str will be looks like this:

"
<data>\n  
<row>\n    <index>0</index>\n    <id>18511</id>\n  </row>\n  
<row>\n    <index>1</index>\n    <id>18671</id>\n  </row>\n  
<row>\n    <index>2</index>\n    <id>18711</id>\n  </row>\n  
<row>\n    <index>3</index>\n    <id>18833</id>\n  </row>\n  
<row>\n    <index>4</index>\n    <id>18965</id>\n  </row>\n
</data>"

I was wondering if some one can shed on some light on why this would happen and how to fix this?

BTW, I'm using

python==3.7
sqlalchemy==1.3.20

Upvotes: 0

Views: 529

Answers (1)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89419

Just remove the PK name to give the PK a unique auto-generated name.

CREATE TABLE #temp
(
  id int not null,
  PRIMARY KEY (id asc),
);

Temporary tables have unique names generated for them in TempDb, but primary key constraints are separate objects, and you can't have two constraints with the same name in a single schema in any database.

So if two different sessions try to create a temp table with the same named constraint, one will fail.

Upvotes: 3

Related Questions