Menu
I have two lines of code in SQL that create two tables on the fly, i need to do something like
IF TABLE EXISTS DROP IT AND CREATE IT AGAIN ELSE CREATE IT my lines are the following ones
CREATE TABLE ##CLIENTS_KEYWORD(client_id int) CREATE TABLE ##TEMP_CLIENTS_KEYWORD(client_id int) how can I apply that concept for these two tables in my procedure?
804
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.
After creation, global temporary tables become visible to any user and any connection. They can be manually dropped with DROP TABLE command. Global temporary tables are automatically dropped when the session that create the table completes and there is no active references to that table.
Option 1 - DROP TABLE if exists using OBJECT_ID() function (all supported versions) Using OBJECT_ID() will return an object id if the name and type passed to it exists.
Consider using the following pattern: BEGIN TRANSACTION; CREATE TABLE #Results; ...; DROP TABLE #Results; COMMIT . If the transaction succeeds, the table will be removed. If it fails, the table will be gone as well (since it was created within the transaction). In any case: No need to check if the table already exists.
From SQL Server 2016 you can just use
DROP TABLE IF EXISTS ##CLIENTS_KEYWORD On previous versions you can use
IF OBJECT_ID('tempdb..##CLIENTS_KEYWORD', 'U') IS NOT NULL /*Then it exists*/ DROP TABLE ##CLIENTS_KEYWORD CREATE TABLE ##CLIENTS_KEYWORD ( client_id INT ) You could also consider truncating the table instead rather than dropping and recreating.
IF OBJECT_ID('tempdb..##CLIENTS_KEYWORD', 'U') IS NOT NULL TRUNCATE TABLE ##CLIENTS_KEYWORD ELSE CREATE TABLE ##CLIENTS_KEYWORD ( client_id INT ) 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
