SELECT INTO a table variable in T-SQL

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 myTableINNER JOIN otherTable ON ...WHERE age > 30;

The purpose of SELECT INTO is (per the docs, my emphasis)

To create a new table from values in another table

But you already have a target table! So what you want is

The INSERT statement adds one or more new rows to a table

You can specify the data values in the following ways:

...

By using a SELECT subquery to specify the data values for one or more rows, such as:

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

And in this syntax, it's allowed for MyTable to be a table variable.

You can also use common table expressions to store temporary datasets. They are more elegant and adhoc friendly:

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