Menu
I have db A and db B. At the beginning of a stored procedure I want to back up all rows from B.mytable to B.mytablebackup. The rest of the stored procedure runs against tables on db A (which gathers data and writes it to B.mytable).
So I check to see if B.mytablebackup exists
IF EXISTS(SELECT 1 FROM B.dbo.mytablebackup)
and if it does, the stored procedure does an
INSERT INTO B..mytablebackup SELECT * FROM B..mytable
If it doesn't exist it does a
SELECT * INTO B..mytablebackup from B..mytable
But when I execute the stored procedure I get the error
There is already an object named 'mytablebackup' in the database
I added a Print statement and execution is taking the "does not exist" branch of the IF.
What am I doing wrong?
271
Using the OBJECT_ID and the IF ELSE statement to check whether a table exists or not. Alternative 2 : Using the INFORMATION_SCHEMA. TABLES and SQL EXISTS Operator to check whether a table exists or not.
Look in sys. dm_db_index_usage_stats. The columns last_user_xxx will contain the last time the table was accessed from user requests. This table resets its tracking after a server restart, so you must leave it running for a while before relying on its data.
The EXISTS operator is used to test for the existence of any record in a subquery. The EXISTS operator returns TRUE if the subquery returns one or more records.
OBJECT_ID can be used too:
IF OBJECT_ID('B.dbo.mytablebackup') IS NOT NULL
For SQL Server, you should use system view sys.tables to check if table exists.
IF EXISTS(SELECT 1 FROM B.sys.tables WHERE name = 'mytablebackup')
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
