Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
In a management studio tab, suppose I run code that creates a temp table.
Now if I re-run this code it gives an error that temp table already exists. I can open a new tab and run this code. This happens because each tab is a new session.
I know that using drop as initial part of the code is a solution. But, is there any way to get a new session (or reset the session) in the same window, so that I don't have to create new tab when re-running the code?
Personally, I just put a DROP IF EXISTS at the front of the code. That makes it forever repeatable, and you don't get errors if you haven't created the table yet.
Otherwise, you can disconnect & reconnect the window without closing it. Pain the bottom, but then you don't have to change the code.
Depending on what you have in your code, you could wrap it into a stored procedure (normal or temporary) and then execute the procedure. Like so:
CREATE OR ALTER PROCEDURE #p
AS
BEGIN
/*your code here*/
/* remember you cannot have GO statements here*/
END
GO
EXEC #p
That way you won't need to write out all the DROP TABLE statements. That being said I personally just reconnect the query window.
You can right click anywhere inside the Query Window, hover over the Connection option, and then click Disconnect, then do the same again and click Connect. This will log you off and back on, leaving the Query Window open, and ultimately creating a new session, which will effectively drop all your TempTables.
