Menu
I have the following issue in SQL Server, I have some code that looks like this:
DROP TABLE #TMPGUARDIAN CREATE TABLE #TMPGUARDIAN( LAST_NAME NVARCHAR(30), FRST_NAME NVARCHAR(30)) SELECT LAST_NAME,FRST_NAME INTO #TMPGUARDIAN FROM TBL_PEOPLE When I do this I get an error 'There is already an object named '#TMPGUARDIAN' in the database'. Can anyone tell me why I am getting this error?
651
Right-click on the database, go to Advanced, and select SQL Database Cleanup Wizard. The SQL Cleanup Wizard will then run. If there are no objects to clean up you will get a message saying there was nothing found.
You cannot associate a trigger with a TEMPORARY table or a view. Trigger names exist in the schema namespace, meaning that all triggers must have unique names within a schema. Triggers in different schemas can have the same name.
Using the DROP TABLE command on a temporary table, as with any table, will delete the table and remove all data. In an SQL server, when you create a temporary table, you need to use the # in front of the name of the table when dropping it, as this indicates the temporary table.
A valuable alternatives for the SQL temp table and table variable are SCHEMA_ONLY Memory-Optimized tables and the Memory-optimized Table Variable, where the data will be completely stored in the memory without the need to touch the TempDB database, providing the best data access performance.
You are dropping it, then creating it, then trying to create it again by using SELECT INTO. Change to:
DROP TABLE #TMPGUARDIAN CREATE TABLE #TMPGUARDIAN( LAST_NAME NVARCHAR(30), FRST_NAME NVARCHAR(30)) INSERT INTO #TMPGUARDIAN SELECT LAST_NAME,FRST_NAME FROM TBL_PEOPLE In MS SQL Server you can create a table without a CREATE TABLE statement by using SELECT INTO
54
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
