sql-server - SELECT INTO T-SQL中的表变量(SELECT INTO a table variable in T-SQL)

Question

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查询，我想从中将所有行插入表变量，但是T-SQL不允许它。)

Along the same lines, you cannot use a table variable with SELECT INTO or INSERT EXEC queries.

(同样，您不能将表变量与SELECT INTO或INSERT EXEC查询一起使用。)

http://odetocode.com/Articles/365.aspx

(http://odetocode.com/Articles/365.aspx)

Short example:

(简短的例子：)

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

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.

(这样做的目的是简单地使脚本比直接在右表中执行SELECT INTO更易读，更容易定制。)

Performance is not an issue, as the rowcount is fairly small and it's only manually run when needed.

(性能不是问题，因为rowcount相当小，并且只在需要时手动运行。)
...or just tell me if I'm doing it all wrong.

(......或者告诉我，如果我做错了。)

  ask by Indrek translate from so

Answer 1

Try something like this:

(尝试这样的事情：)

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;