SELECT INTO a table variable in T-SQL

Tag: , , , ,

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.
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.
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.

4 Answers

  1. arora on Mar 05, 2013

    Try like this:

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

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

  2. arora on Mar 05, 2013

    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.

  3. harry on Mar 05, 2013

    Try instead INSERT @UserData
    SELECT name, location etc.

  4. cheeseballlumpy82 on Mar 05, 2013

    Why dont you use temporary tables…

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

    you dont have to declare the table that way

    The understanding of what([table or variable][1]) to use really helps..
    http://databases.aspfaq.com/database/should-i-use-a-temp-table-or-a-table-variable.html