Tempdb dropping tables which running multiple .sql scripts from directory

Hi Experts !
I have 11 sqlfiles ( create table1 to table11 and insert statements in each respectively). When execute one by one is SSMS its ok .
But while batch executing .sql files from directory the table e.g table1 create and after next Table2 execution script it drops the Table1 of Tablescript1 and so on after execution of 11.sql file the last executed 11.sql file which is table11 appears in temporary tables. other tables are dropped automatically?
image

image

@Sameer Could you please post the DDL part of the temp tables from one of your .sql files?
For example script 1 and script 2 and also read this

https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms186986(v=sql.105)?redirectedfrom=MSDN#temporary-tables

According to the first graphic above, you're using Global Temporary tables. If the session that created the table ends and no other session is using a given Global Temporary table, it will automatically drop.

2 Likes

This behavior is to be expected. Temporary tables are automatically dropped when the session that creates them ends (global temp tables -- i.e. the ones that you are using -- can exist slightly longer if there are other sessions accessing them):

  • This works as you expect it to in SSMS because you are executing them either in the same session / tab, or if executing them in their own tabs, then you are keeping all tabs open until you are done.
  • When batch executing them, each session ends before the next session is started.

For the batch approach to work as you are wanting (temp tables created in first script remain until last script is done), you will need to concatenate the scripts together so that they execute in the same session.

Side note: Looking at the order in which your scripts are being executed, you should left-pad order numbers with 0s so that all numbers are always the same number of digits. Hence, use Table01.sql* instead of Table1.sql. Then they will sort in the expected order. Do not go by what you see in File Explorer since that program uses "natural" sorting which can sort numeric values within strings as if they were actual numbers, but doing file sorting in BAT / CMD scripts uses the simple sorting that you see in your SQLCMD screenshot.

2 Likes

Hi Sirs,
Jeffmoden Thank you for your reply . i got the point.
Solomon Rutzky much understood thank you for your comprehensive information very much clear to me.
Side note : I am feeling happy for advices and flagged them as transferring experts knowledge. :clap:

You're a gentleman and a scholar, Sameer. Thank you for the feedback and the good mark.