SQL Server临时表消失 [英] Sql Server temporary table disappears

问题描述

我正在创建一个临时表#ua_temp，它是常规表的子集.我没有收到错误，但是当我尝试在第二步中从#ua_temp中进行选择时，找不到该错误.如果删除#，则会创建一个名为ua_temp的表.

I'm creating a temporary table, #ua_temp, which is a subset of regular table. I don't get an error, but when I try to SELECT from #ua_temp in the second step, it's not found. If I remove the #, a table named ua_temp is created.

我使用了与其他地方的SELECT INTO创建表完全相同的技术.它运行良好，所以我认为它与数据库设置无关.有人可以看到问题吗?

I've used the exact same technique from created the table with SELECT INTO elsewhere. It runs 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");

要回答一些明显的问题:使用源表elig_ua_response，此程序运行良好.引入临时表的原因是我想删除此特定报告的某些行.在测试时，我在[文件名]列旁边放置了方括号，以确保这不是关键字问题.如果将#ua_temp替换为elig_ua_response，则第二个SELECT可以正常工作.我为临时表尝试了不同的名称.显示行数的MessageBox仅用于调试目的；它不会影响问题.

To answer some of the obvious questions: This program was running fine using the source table, elig_ua_response. The reason for introducing the temp table was that I want to delete some of the rows for this particular report. I put brackets around the column [filename] while testing to be sure it's not a key word issue. The second SELECT works fine if you replace #ua_temp with elig_ua_response. I've tried different names for the temp table. The MessageBox showing the number of rows was just for debugging purposes; it doesn't affect the problem.

推荐答案

我认为您的问题的解决方案是将临时表的创建和从该临时表中进行选择组合到一个查询中(请参见下面的代码段3)..如果不使用命令参数，则两次执行命令(就像在问题代码中一样)似乎可以正常运行，但是如果引入了命令参数，则执行失败.我测试了几种不同的方法，这就是我发现的结果.

I think the solution to your problem is to combine the creation of the temp table and selecting from that temp table into one query (see code snippet #3 below). Executing the command twice (as you do in the code in your question) seems to work ok if you are not using command parameters, but fails if they are introduced. I tested a few different approaches and here's what I found.

1)工作正常:使用相同的命令对象，不使用命令参数，两次执行命令:

1) WORKS OK: Use same command object, no command parameters, execute 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)失败:使用相同的命令对象，命令参数，两次执行命令:

2) FAILS: Use same command object, command parameters, execute 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)工作正常:使用相同的命令对象，命令参数，仅执行一次命令:

3) WORKS OK: Use same command object, command parameters, execute command once only:

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"]);
        }
    }
}

这篇关于SQL Server临时表消失的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！