Sql server temporary table disappears

Question

I am creating a temporary table, #ua_temp, which is a subset of a regular table. I do not receive an error message, but when I try to SELECT from #ua_temp in the second step, it is not found. If I delete #, a table called ua_temp will be created.

I used the same technique from the created table with SELECT INTO elsewhere. It works fine, so I don't think it has anything to do with database settings. Can anyone see the problem?

// Create temporary table q = new StringBuilder(200); q.Append("select policy_no, name, amt_due, due_date, hic, grp, eff_dt, lis_prem, lis_grp, lis_co_pay_lvl, "); q.Append("lep_prem, lapsed, dn_code, [filename], created_dt, created_by "); q.Append("into #ua_temp from elig_ua_response "); q.Append("where [filename] = @fn1 or [filename] = @fn2 "); sc = new SqlCommand(q.ToString(), db); sc.Parameters.Add(new SqlParameter("@fn1", sFn)); sc.Parameters.Add(new SqlParameter("@fn2", sFn2)); int r = sc.ExecuteNonQuery(); MessageBox.Show(r.ToString() + " rows"); // Rosters q = new StringBuilder(200); q.Append("select policy_no,name,amt_due,due_date,hic,grp,eff_dt,"); q.Append("lis_prem,lis_grp,lis_co_pay_lvl,lep_prem,lapsed,dn_code,[filename] "); q.Append("from #ua_temp where (lis_prem > 0.00 or lep_prem > 0.00) "); q.Append("and [filename] = @fn order by name"); sc.CommandText = q.ToString(); sc.Parameters.Clear(); sc.Parameters.Add(new SqlParameter("@fn", sFn)); sda = new SqlDataAdapter(sc); sda.Fill(ds, "LIS LEP Roster"); 

To answer some obvious questions: this program worked fine using the original elig_ua_response table. The reason for introducing the temporary table was that I want to delete some rows for this particular report. I put brackets around the [file_name] column during testing to make sure this is not a keyword. The second SELECT works fine if you replace #ua_temp with elig_ua_response. I tried different names for temp table. A MessageBox showing the number of lines is for debugging purposes only; this does not affect the problem.

Answer 1

I think that the solution to your problem is to combine the creation of the temp table and select from this temporary table into one query (see code snippet No. 3 below). Running the command twice (as in the code in your question) seems to work fine if you don't use the command options, but fail if they are entered. I tested several different approaches, and here is what I found.

1) WORKS OK : use the same command object, without command parameters, execute the command twice:

 using (var conn = new SqlConnection("...")) { conn.Open(); using (var cmd = conn.CreateCommand()) { const string query = @" CREATE TABLE #temp ([ID] INT NOT NULL, [Name] VARCHAR(20) NOT NULL) INSERT INTO #temp VALUES(1, 'User 1') INSERT INTO #temp VALUES(2, 'User 2')"; cmd.CommandType = CommandType.Text; cmd.CommandText = query; cmd.ExecuteNonQuery(); cmd.CommandText = "SELECT * FROM #temp"; using (var sda = new SqlDataAdapter(cmd)) { var ds = new DataSet(); sda.Fill(ds); foreach (DataRow row in ds.Tables[0].Rows) Console.WriteLine("{0} - {1}", row["ID"], row["Name"]); } } } 

2) FAILS : use the same command object, command parameters, execute the command twice:

 using (var conn = new SqlConnection("...")) { conn.Open(); using (var cmd = conn.CreateCommand()) { const string query = @" CREATE TABLE #temp ([ID] INT NOT NULL, [Name] VARCHAR(20) NOT NULL) INSERT INTO #temp VALUES(1, @username1) INSERT INTO #temp VALUES(2, @username2) "; cmd.CommandType = CommandType.Text; cmd.CommandText = query; cmd.Parameters.Add("@username1", SqlDbType.VarChar).Value ="First User"; cmd.Parameters.Add("@username2", SqlDbType.VarChar).Value ="Second User"; cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); cmd.CommandText = "SELECT * FROM #temp"; using(var sda = new SqlDataAdapter(cmd)) { var ds = new DataSet(); sda.Fill(ds); foreach(DataRow row in ds.Tables[0].Rows) Console.WriteLine("{0} - {1}", row["ID"], row["Name"]); } } } 

3) WORKS OK : use the same command object, command parameters, only the execution command:

 using (var conn = new SqlConnection("...")) { conn.Open(); using (var cmd = conn.CreateCommand()) { const string query = @" CREATE TABLE #temp ([ID] INT NOT NULL, [Name] VARCHAR(20) NOT NULL) INSERT INTO #temp VALUES(1, @username1) INSERT INTO #temp VALUES(2, @username2) SELECT * FROM #temp "; cmd.CommandType = CommandType.Text; cmd.CommandText = query; cmd.Parameters.Add("@username1", SqlDbType.VarChar).Value ="First User"; cmd.Parameters.Add("@username2", SqlDbType.VarChar).Value ="Second User"; using (var sda = new SqlDataAdapter(cmd)) { var ds = new DataSet(); sda.Fill(ds); foreach (DataRow row in ds.Tables[0].Rows) Console.WriteLine("{0} - {1}", row["ID"], row["Name"]); } } } 

Answer 2

It works. Apparently, if SqlParameters are in the step that creates the table, the table is not left for the next step. After creating the table, SqlParameters can be used in a separate step for INSERT.

  // Create temporary file dropping members from termed groups. q = new StringBuilder(500); q.Append("create table #ua_param "); q.Append("([ID] int not null, fn varchar(50) not null) "); sc = new SqlCommand(q.ToString(), db); sc.ExecuteNonQuery(); q = new StringBuilder(500); q.Append("insert into #ua_param values(1,@fn1) "); q.Append("insert into #ua_param values(2,@fn2) "); sc = new SqlCommand(q.ToString(), db); sc.Parameters.Add(new SqlParameter("@fn1", sFn)); sc.Parameters.Add(new SqlParameter("@fn2", sFn2)); sc.ExecuteNonQuery(); q = new StringBuilder(500); q.Append("select policy_no, name, amt_due, due_date, hic, grp, eff_dt, lis_prem, lis_grp, lis_co_pay_lvl, "); q.Append("lep_prem, lapsed, dn_code, [filename], created_dt, created_by "); q.Append("into #ua_temp from elig_ua_response inner join #ua_param on [filename] = fn "); sc.Parameters.Clear(); sc.CommandText = q.ToString(); sc.CommandTimeout = 1800; sc.ExecuteNonQuery(); 

Answer 3

This is because the temp table is simple. Temporary. You may consider performing operations in a stored procedure.

Answer 4

#TEMP tables are available in only one session or in SPID. Therefore, if you want to reuse it, you need to reuse the connection that you used to create it.

Answer 5

I had the same problem. I tried the SeaDrive solution and it works, however my tests make me believe that the query execution "resets" something between "ADO.NET/SQLDriver" and MS SQL Server.

So, you need to isolate the "CREATE TABLE" statement and send it to the database before using it with "INSERT INTO". Composed commands connecting CREATE and INSERT in one unique statement do not work if you cannot refuse the parameters.

Answer 6

Joe Zack's comment helped me understand what is going on here. Very clear and concise explanation. This should be the answer to make it more visible to people arriving here from a Google search.

SqlCommand calls sql with parameters via sp_executesql when there are parameters, which means that your temporary table is created inside (and then cleared) by the stored procedure, so it is not available for future calls - even when they use the same connection

Answer 7

Besides being translated into a stored procedure suggested by @Daniel A White, you can look at the BOL article and find global temporary tables . Also write briefly on Temporary Tables . Any approach should maintain a temporary table.

Answer 8

Using a stored procedure makes sense for this kind of thing.

If for some reason this is not possible, make sure that you use the same connection to create the temp table as to select the temp table, otherwise the temp table will not be visible. (This problem may occur randomly if you use a connection pool.) Alternatively, use a real physical table or even a global temp table (## global_tmp vs #local_tmp), but in any case you will need to develop a scheme / protocol so that several processes did not try to create / delete / write to this table.

Again, I want to emphasize that the stored procedure will be a good way, if possible.