CODEWITHSUNDEEP
sql-serversqlcmd
Metaphor
Metaphor

Reputation: 6415

Global temp tables with SQLCMD

In SSMS, I can create a global temp variable in one batch and use it in another. Like this:

CREATE TABLE ##temp (col1 INT)
GO

DROP TABLE ##temp
GO

With SQLCMD, I create a global temp table in one call and it does not exist in the second.

sqlcmd -S localhost -d tempdb -E -Q "create table ##temp (col1 int)"

sqlcmd -S localhost -d tempdb -E -Q "drop table ##temp"
Msg 3701, Level 11, State 5, Server VATLLXT7LGBARE2, Line 1
Cannot drop the table '##temp', because it does not exist or you do not have permission.

Is a global temp table not supposed to stick around until the server is reset?

Upvotes: 2

Views: 3012

Answers (1)

Rahul
Rahul

Reputation: 77896

Yes that's correct cause global temporary table exists in the connection/session that created it and gets dropped automatically if the connection closes. In your case, first SQLCMD creates the table

sqlcmd -S localhost -d tempdb -E -Q "create table ##temp (col1 int)"

And the connection closes which drops the temporary table and so in next connection it doesn't exists anymore

sqlcmd -S localhost -d tempdb -E -Q "drop table ##temp"

Upvotes: 5

Related Questions