SQL Server 临时表消失

Question

我正在创建一个临时表 #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 仅用于调试目的；它不会影响问题。

- SeaDrive

---

运行SQL Server Profiler，有一个名为SPID的列。如果两个语句具有不同的SPID，则说明存在作用域问题。 - Jeremy Gray

你能否将跟踪中的文本复制出来，在管理工作室中运行这两个语句吗？ - Jeremy Gray

我将它们复制到了Sql Query Analyzer中。第一个没有错误，但没有创建表，所以第二个失败了。使用##global_temp可以正常工作。 - SeaDrive

在创建步骤中似乎存在问题，我尝试将 SqlParameters 从第一个命令中取出，程序运行正常。 - SeaDrive

我正在检查rsbarro的答案，但同样要感谢Jeremy Gray让我更仔细地研究了SELECT INTO，并让我开始关注SqlParameters。 - SeaDrive

Answer 1

Joe Zack的评论帮助我理解了这里发生的事情。非常清晰而简洁的解释。这应该成为一个答案，以便那些通过Google搜索到达此处的人更容易看到。

当有参数时，SqlCommand通过sp_executesql以参数方式调用sql，这意味着您的临时表在存储过程内部创建（然后清理），因此即使它们共享同一连接，也无法对未来的调用可用。

- David Tarulli

---

是的 - 在没有参数的命令中创建您的临时表。 然后，您可以在具有参数的命令中无限查询它。 - NYCdotNet

Answer 2

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

1）能够正常工作：使用相同的命令对象，没有命令参数，执行两次命令：

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) 运行良好：使用相同的命令对象、命令参数，仅执行一次命令：

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

- rsbarro

---

谢谢你的努力。我将尝试你的第三个建议。我会创建一个小的临时表来保存这两个参数，然后使用连接将大表限制为所需的行。 - SeaDrive

@SeaDrive 你也可以考虑在WHERE子句中使用EXISTS来避免完全使用临时表。我没有在答案中提到这一点，因为我只是试图回答问题。祝你好运。 - rsbarro

事实证明，选项3不符合我的需求，因为临时表不能用于其他的SELECT操作。我将其分成了三个步骤来使其运行。 - SeaDrive

Answer 3

这个方法可行。显然，如果SqlParameters在创建表的步骤中，那么表将不会留给下一个步骤。一旦表被创建，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();

- SeaDrive

---

当存在参数时，SqlCommand通过sp_executesql调用带有参数的SQL语句，这意味着在存储过程中创建了临时表（然后进行清理），因此即使它们共享同一连接，未来的调用也无法访问该临时表。 - Joe Zack

Answer 4

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

- Daniel A. White

---

@Seadrive - 如果你将这段代码放入存储过程中，问题就会消失，因为存储过程在执行期间会维护该表。 - JNK

@SeaDrive - 如果这个回答对你没有帮助，那就给我点个踩吧。但是我仍然坚持这个答案。 - Daniel A. White

这是对问题的回答，而不是对于提问本身的回答。 - SeaDrive

由数据库决定表格的操作。 - Daniel A. White

只要使用相同的连接，将同一临时表用于不同的命令是完全有效的。问题在于创建临时表的sqlcommand带有参数，然后该sql通过sp_executesql执行 - 这会封闭临时表并将其删除。 - Joe Zack

Answer 5

除了像 @Daniel A White 建议的那样将其编写为存储过程之外，您还可以查看 BOL 文章并搜索“全局临时表”以及有关临时表的简要介绍。任何一种方法都应该使临时表保持活动状态。

- billinkc

---

全局临时表不起作用，因为在第二个连接访问它之前，发起连接即将关闭。它仍然会在查询之间被删除。 - JNK

我的错，我想这就是当我没有编写代码验证我的答案时会发生的事情。 - billinkc

实际上，全局临时表确实起作用了，这在某种程度上令人费解。 - SeaDrive

@Bill - 如果你查看BOL关于##globaltemptables的内容，也可以找到它，但是它们只会持续存在于调用会话处于活动状态或正在被访问的情况下。 - JNK

Answer 6

我遇到了同样的问题。我尝试了SeaDrive的解决方案，它有效果，但是我的测试让我相信，在“ADO.NET / SQLDriver”和MS SQL Server之间的查询执行中会有一些东西“刷新”。

因此，您需要先将“CREATE TABLE”语句隔离出来并提交到数据库，然后再将其与“INSERT INTO”一起使用。将CREATE和INSERT组合成一个唯一的命令不起作用，除非您可以放弃参数。

- Luciano

Answer 7

#TEMP表只能在同一会话或SPID中访问。因此，如果您想要重复使用它，需要重复使用生成它的连接。

- JNK

---

它使用相同的SqlCommand和相同的SqlConnection，没有任何干扰代码。 - SeaDrive

@Seadrive - 这并不是在重复使用连接，否则第二个命令将会看到该表。 - JNK

如果不行，那为什么呢？我以前用过这个技巧而没有遇到问题。 - SeaDrive

SqlDataAdapters在技术运作的地方以相同的方式处理。我也尝试过不使用SqlParameters，但没有任何区别。 - SeaDrive

我认为这可能与SqlCommand文本的结构有关。如果它是一个简单的select into，没有参数，那么它可以作为一个简单的select语句运行，因此它不会被包装在像'sp_executesql'这样的SqlProcedure中，所以它将保持对使用相同SqlCommand和SqlConnection对象的后续查询可见。另一方面，如果它是一个复杂的语句，临时表可能会在类似于'sp_executesql'的存储过程中创建，并且在命令完成时将超出范围。 - Triynko

Answer 8

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
}

- NYCdotNet

---

谢谢，其他答案都没有明确指出CREATE临时表SQL不能有参数，这一点让我卡住了。 - PandaWood

Answer 9

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

如果由于某些原因不可行，则确保在创建临时表和选择临时表时使用相同的连接，否则临时表将不可见。（如果使用连接池，则可能会随机出现此问题。）或者，使用真实的物理表甚至全局临时表（##global_tmp vs #local_tmp），但无论哪种情况，您都需要设计一种方案/协议，以使多个进程不会尝试创建/删除/写入该表。

再次强调，如果可能的话，使用存储过程是一个好的选择。

- Garrett

---

它可以使用##global_temp。为什么会这样？我是开发机器（笔记本电脑）上唯一的用户。 - SeaDrive

抱歉晚回复。可能是因为使用了全局（而不是本地）临时表，因为全局临时表可在连接之间共享。 - Garrett