在T-SQL中选择表变量 [英] SELECT INTO a table variable in T-SQL

问题描述

有一个复杂的SELECT查询，我想从中将所有行插入表变量中，但T-SQL不允许.

Got a complex SELECT query, from which I would like to insert all rows into a table variable, but T-SQL doesn't allow it.

沿着同一行，不能将表变量用于SELECT INTO或INSERT EXEC查询. http://odetocode.com/Articles/365.aspx

Along the same lines, you cannot use a table variable with SELECT INTO or INSERT EXEC queries. 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很小，只能在需要时手动运行.
...或者只是告诉我我做错了什么.

The data in the table variable would be later used to insert/update it back into different tables (mostly copy of the same data with minor updates). The goal of this would be to simply make the script a bit more readable and more easily customisable than doing the SELECT INTO directly into the right tables. Performance is not an issue, as the rowcount is fairly small and it's only manually run when needed.
...or just tell me if I'm doing it all wrong.

推荐答案

尝试如下操作:

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;

这篇关于在T-SQL中选择表变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！