SELECT INTO a table variable in 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.
Along the same lines, you cannot use a table variable with SELECT INTO or INSERT EXEC queries.
declare @userData TABLE(
name varchar(30) NOT NULL,
oldlocation varchar(30) NOT NULL
SELECT name, location INTO @userData FROM myTable
INNER JOIN otherTable ON ...
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.