在T-SQL中将SELECT语句的结果插入到表变量中

Question

收到了一个复杂的SELECT查询语句，想要将所有行插入到一个表变量中，但是T-SQL不允许这样做。

同样的，你也不能在SELECT INTO或INSERT EXEC查询中使用表变量。http://odetocode.com/Articles/365.aspx

以下是简短的示例：

declare @userData TABLE(
                        name varchar(30) NOT NULL,
                        oldlocation varchar(30) NOT NULL
                       )

SELECT name, location
INTO @userData
FROM myTable
    INNER JOIN otherTable ON ...
WHERE age > 30

表变量中的数据稍后将用于将其插入/更新到不同的表中（大多数是具有轻微更新的相同数据副本）。这样做的目的仅是使脚本更易读并且比直接将SELECT INTO放入正确的表中更易定制。

性能不是问题，因为rowcount相当小，并且只在需要时手动运行。...或者告诉我是否完全错误。

- Indrek

Answer 1

尝试类似于以下内容：

DECLARE @userData TABLE(
    name varchar(30) NOT NULL,
    oldlocation varchar(30) NOT NULL
);

INSERT INTO @userData (name, oldlocation)
SELECT name, location FROM myTable
INNER JOIN otherTable ON ...
WHERE age > 30;

- CristiC

---

如果您选择“SELECT name，location FROM myTable”作为要插入UserData表的值，则选择命名与表定义中的名称相符的变量是否很重要。您正在选择将“name”放入UserData 'name'变量中，但是您在选择“location”并以某种方式将其分配给UserData 'oldlocation'变量。 SQL会自动映射这些内容还是会引发某种异常？ - Aran Mulholland

不管名称，只要列类型正确即可。 - CristiC

哇，这有点讲得通，但同时我作为语法解析器感觉有点受伤了 :) - Aran Mulholland

我似乎无法在UPDATE语句中使用这个：gist链接 - Paul-Sebastian Manole

在插入语句中，如果您没有明确声明列，则它们将按照原始创建表语句中声明的顺序进行映射，就像选择*一样。因此，在选择语句中，位置被映射到@userData表中的oldlocation，因为位置在选择的结果集中处于第2个位置，而oldlocation是表定义中的第2个列。话虽如此，永远不要这样做。不能依赖数据库对列或行的排序。始终要明确此点。 - absmiths

Answer 2

SELECT ... FROM ... WHERE ... INSERT INTO table_name ...

  INSERT INTO MyTable 
 (PriKey, Description)
        SELECT ForeignKey, Description
        FROM SomeView

在这种语法下，允许MyTable是一个表变量。

- AakashM

---

真希望被采纳的答案包含了这些信息！ - Davie Brown

我执行这个操作时，出现了"MyTable是无效的对象名称"的错误，因此这个答案缺少某些内容。 - Mike Flynn

@MikeFlynn 在这里，MyTable是您实际表格名称的占位符。我认为没有真正的数据库使用名为MyTable的表格... - AakashM

如果我想使用SELECT INTO创建/声明一个表变量怎么办？ 例如，将表变量的列定义为t1.somecolumn、t1.othercolumn、t2.*。 - Alicia

啊，但这正是我想做的事情！我想从另一个表中的值创建一个表变量，这样我就不必在创建表时和插入时分别指定表的列三次。 - Max Hay

@MaxHay 听起来你可能想要提出一个新问题… - AakashM

Answer 3

您可以使用公共表达式来存储临时数据集。它们更加优雅和适应性强：

WITH userData (name, oldlocation)
AS
(
  SELECT name, location 
  FROM   myTable    INNER JOIN 
         otherTable ON ...
  WHERE  age>30
)
SELECT * 
FROM   userData -- you can also reuse the recordset in subqueries and joins

- nanestev

---

我不认为这会复制数据，如果你从userData中删除或更新数据，会不会影响到你原始表中的记录？ - atreeon

是的，在CTE上执行DELETE和UPDATE操作只要该CTE没有使用连接、并集等引用多个表，就会修改源表。 - nanestev

这样做的缺点是，您只能在紧随其后的命令中使用CTE表。如果由于任何原因需要对结果集进行多次遍历，则CTE将无法使用。OP似乎暗示将进行多个修改，在这种情况下，它将无法正常工作 - “表变量中的数据稍后将用于将其插入/更新到不同的表中（大多数都是相同数据的副本，带有轻微的更新）”。 - Tony

使用CTE的真正缺点出现在连接从大表中选择的CTE时。通过使用表变量而不是CTE，我能够将处理时间缩短了两个数量级以上（来源：500万行，收益：>30分钟到5秒）！ - mzuther

Answer 4

如果您不是从应用程序中执行此操作，可以尝试使用临时表。 （手动运行可能没问题）

SELECT name, location INTO #userData FROM myTable
INNER JOIN otherTable ON ...
WHERE age>30

如果您不想声明表格，可以通过创建本地临时表来帮助进行adhoc查询。这种方式创建的表格只能在相同会话中可见，对于从应用程序运行查询可能会有问题。

如果您需要在应用程序上运行查询，请使用通过变量声明的方式：

DECLARE @userData TABLE(
    name varchar(30) NOT NULL,
    oldlocation varchar(30) NOT NULL
);

INSERT INTO @userData
SELECT name, location FROM myTable
INNER JOIN otherTable ON ...
WHERE age > 30;

编辑：正如你们中的许多人提到的，将可见性更新为来自连接的会话。对于Web应用程序，创建临时表不是一个选项，因为会话可以被重用，在这些情况下使用临时变量。

- Whimsical

---

抱歉，我忘记提到我没有创建表的权限。 - Indrek

创建一个临时变量会增加一些开销。 - paparazzo

使用临时表并不总是安全的。例如，在Web服务中，通过单个连接限制服务器上的最大连接并更好地保护SQL，临时表将存在于通过的每个查询中，并可能覆盖当前正在使用它的某个人。 - Franck

如果您使用全局临时表（两个哈希前缀），那么您是正确的。但是，本地临时表（一个哈希前缀）将仅与单个会话（即单个连接）隔离，因此除非您为所有请求使用单个连接（不建议），否则不会出现您所暗示的并发问题。然而，可能存在性能影响。 - maf748

@GazB 当然，任何具有副作用的语句都不能在“函数”中使用。根据我的经验，在大多数情况下，当有人认为他们需要这样的语句时，实际上意味着他们应该重新考虑他们的“函数” - 或者至少重构为“过程”。至少我是这么认为的。 :-) - underscore_d

你把“连接”和“会话”搞混了。临时表的作用域是“会话”。 - Jamie Marshall

Answer 5

尝试使用INSERT而不是SELECT INTO：

   DECLARE @UserData TABLE(
                        name varchar(30) NOT NULL,
                        oldlocation varchar(30) NOT NULL
                       )

    INSERT @UserData   
    SELECT name, oldlocation

- Zephyr was a Friend of Mine

---

如果你没有先声明表变量，那么就不能使用它，这也是我试图避免的：https://i.imgur.com/2PXIubF.png - sǝɯɐſ

Answer 6

首先创建一个临时表： 步骤1：

create table #tblOm_Temp (

    Name varchar(100),
    Age Int ,
    RollNumber bigint
)

**步骤2：** 在临时表中插入一些值。

insert into #tblom_temp values('Om Pandey',102,1347)

第三步: 声明一个表格变量来存储临时表数据。

declare   @tblOm_Variable table(

    Name Varchar(100),
    Age int,
    RollNumber bigint
)

步骤 4：从临时表中选择值并插入到表变量中。

insert into @tblOm_Variable select * from #tblom_temp

最后，将临时表中的值插入到表变量中。

步骤5： 可以检查已插入到表变量中的值。

select * from @tblOm_Variable

- 404 Not found

Answer 7

好的，现在我已经付出足够的努力，能够使用以下方式插入到@table中：

INSERT @TempWithheldTable SELECT
a.SuspendedReason, a.SuspendedNotes, a.SuspendedBy , a.ReasonCode FROM OPENROWSET( BULK 'C:\DataBases\WithHeld.csv', FORMATFILE = N'C:\DataBases\Format.txt',
ERRORFILE=N'C:\Temp\MovieLensRatings.txt' ) AS a;

这里的主要问题是选择要插入的列。

- RahulJha

---

我收到了一个“必须声明表变量“@TempWithheldTable”的编译错误消息。 - atreeon

Answer 8

使用SELECT INTO的一个原因是它允许您使用IDENTITY：

SELECT IDENTITY(INT,1,1) AS Id, name
INTO #MyTable 
FROM (SELECT name FROM AnotherTable) AS t

这对于表变量来说是行不通的，这太糟糕了...

- MOHCTP

---

虽然如此，您仍可以使用IDENTITY列声明一个表变量。 - Martin Smith