CODEWITHSUNDEEP
sql-serversql-server-2012
Jeremy
Jeremy

Reputation: 121

Unable to copy data to SQL server - "There is already an object named in the database."

I've got a table in a legacy non-sql program that has a routine to copy the data into a SQL Server database. The developer of this system wrote the routine with no documentation to see what is happening under the hood. One table I attempted to move ended up failing, now when I try to copy this table over I get the following error:

Unable to create table 'CR_Receipt_Payment_Details'

42S01: [Microsoft][ODBC SQL Server Driver][SQL Server] There is already an object named 'byReceipt_RECPMT' in the database.

I know that I need to delete this object so that the process can run. I tried running

Drop Table byReceipt_RECPMT

and have the following output:

Cannot use DROP TABLE with 'byReceipt_RECPMT' because 'byReceipt_RECPMT' is a constraint. Use ALTER TABLE DROP CONSTRAINT.

I'm not sure where to go from here.

Can someone point me in the direction of how to find and drop this object?

Thanks.

Upvotes: 0

Views: 2722

Answers (2)

Ed Gibbs
Ed Gibbs

Reputation: 26343

This means that your database has a constraint (primary key, foreign key, check, etc.) named byReceipt_RECPMT. Because that name is being used by a constraint, it can't be used by a table.

You could drop the constraint, but so far the only reason you have is that you want to use its name for a table. That's not good enough. If you investigate the constraint and find that it's incorrect or that you don't need it, by all means drop it - Ross Presser's answer shows how to do so.

If you do need the constraint, either rename the constraint or use a different table name. To rename a constraint, use the sp_rename system stored procedure:

sp_rename 'CR_Receipt_Payment_Details.byReceipt_RECPMT', 'new constraint name'

sp_rename documentation (There are examples in the section titled "Renaming constraints").

Upvotes: 1

Ross Presser
Ross Presser

Reputation: 6255

If the table already exists, then you want to do this:

ALTER TABLE CR_Receipt_Payment_Details DROP CONSTRAINT byReceipt_RECPMT

However, the problem may be that there is a constraint with that name on a DIFFERENT table. You need to find out what table that is. Or else, rename the constraint for THIS table.

Upvotes: 2

Related Questions