Sql Server临时表消失

本文关键字：消失 临时表 Server Sql | 更新日期: 2023-09-27 18:01:31

我正在创建一个临时表#ua_temp，它是常规表的一个子集。我没有得到一个错误，但是当我在第二步中尝试从#ua_temp中SELECT时，没有找到它。如果我删除#，将创建一个名为ua_temp的表。

我在其他地方使用了与创建SELECT INTO表完全相同的技术。它运行良好，所以我不认为它与数据库设置有任何关系。有人能看出问题所在吗?

        // 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，程序运行良好。引入临时表的原因是我想为这个特定的报告删除一些行。我在列[filename]周围加上括号，同时测试以确保这不是一个关键字问题。如果将#ua_temp替换为elig_ua_response，则第二个SELECT可以正常工作。我为临时表尝试了不同的名称。显示行数的MessageBox只是为了调试目的;这对问题没有影响。

Sql Server临时表消失

Joe Zack的评论帮助我理解了这里发生的事情。一个非常清晰和简洁的解释。这应该是一个答案，以便从谷歌搜索到这里的人更容易看到。

当有参数时，SqlCommand通过sp_executesql调用带有参数的sql，这意味着您的临时表将在存储过程中创建(然后在存储过程中清理)，因此它不可用于未来的调用-即使它们共享相同的连接

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

1) WORKS OK:使用相同的命令对象，没有命令参数，执行命令两次:

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) 失败:使用相同的命令对象，命令参数，执行命令两次:

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:使用相同的命令对象，命令参数，只执行一次命令:

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

可以。显然，如果SqlParameters在创建表的步骤中，那么表就不会留在下一步中。一旦创建了表，就可以在INSERT的单独步骤中使用SqlParameters。

        // 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();

这是因为临时表就是这样。暂时的。您可以考虑在存储过程中执行操作。

除了按照@Daniel a White的建议将其滚入存储过程之外，您还可以查看BOL文章并搜索全局临时表。还有关于临时表的简短介绍。这两种方法都应该使临时表保持活动状态。

我也遇到了同样的问题。我尝试了SeaDrive解决方案，它的工作原理，但我的测试让我相信，查询执行"冲洗"之间的东西ADO。. NET/SQLDriver"和MS SQL Server.

因此，您需要隔离"CREATE TABLE"语句并将其提交给数据库，然后才能将其与"INSERT INTO"一起使用。在一个唯一的语句中连接CREATE和INSERT的组合命令不能工作，除非您可以放弃参数。

#TEMP表只能在同一个会话或SPID中访问。所以如果你想重用它，你需要重用你用来生成它的连接。

使用Dapper的工作示例:

using (var conn = new SqlConnection(connectionString))
{
    conn.Open();
    var expected = Guid.NewGuid();
    // creating the temp table with NO PARAMETERS PASSED IN is the key part.
    conn.Execute("CREATE TABLE #MyTemp (ID UNIQUEIDENTIFIER NOT NULL PRIMARY KEY);");
    // now that the temp table is created, you can run queries with params as
    // much as you want.
    conn.Execute("INSERT INTO #MyTemp (ID) VALUES (@ID)", new { ID = expected });
    var actual = conn.Query<Guid>("SELECT ID FROM #MyTemp;").Single();
    Assert.Equal(expected, actual); // proof it worked
}

对于这种事情使用存储过程是有意义的。

如果由于某种原因无法实现，那么请确保在创建临时表时使用的连接与选择临时表时使用的连接相同，否则临时表将不可见。(如果您使用连接池，可能会随机出现此问题。)或者，使用一个真实的物理表，甚至是一个全局临时表(##global_tmp vs #local_tmp)，但在任何一种情况下，您都需要设计一个方案/协议，使多个进程不会试图创建/删除/写入该表。

我要再次强调，如果可能的话，存储过程是一个很好的途径。

相关文章：