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.

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.

Solution 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;

Solution 2

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.

Solution 3

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

Solution 4

You could try using temporary tables...if you are not doing it from an application. (It may be ok to run this manually)

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

You skip the effort to declare the table that way... Helps for adhoc queries...This creates a local temp table which wont be visible to other sessions unless you are in the same session. Maybe a problem if you are running query from an app.

if you require it to running on an app, use variables declared this way :

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

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

Edit: as many of you mentioned updated visibility to session from connection. Creating temp tables is not an option for web applications, as sessions can be reused, stick to temp variables in those cases

Solution 5

Try to use INSERT instead of SELECT INTO:

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

    INSERT @UserData   
    SELECT name, oldlocation

View more solutions

Comments

[Indrek]

Sorry, forgot to mention I don't have rights for CREATE TABLE.

[paparazzo]

Creating a temp has a little more overhead.

[Martin Smith]

You can declare a table variable with an IDENTITY column though.

[Franck]

using temp table is not always safe. For instance, web services. With webservices with a single connection to limit the max connection on the server AND protectign SQL a bit more, the temp table will exist for EVERY query passing through and can overwrite someone currently using it.

[maf748]

@Franck - if you use a global temp table (two hash prefix) you are correct. However, a local temp table (one hash prefix) will be isolated to a single session (aka single connection) so there won't be the concurrency issues you're alluding to unless you're using a single connection for all requests (not advised). The possible performance implications remain, though.

[underscore_d]

@GazB Sure, any statement with a side-effect is excluded from being used in a function. In my experience, in most cases where someone thinks they need such statements, this actually means they should rethink their function – or at least refactor to a procedure. Speaking for myself, at least. :-)

[Davie Brown]

Really wish the accepted answer included this info!

[Aran Mulholland]

If you "SELECT name, location FROM myTable" as the values you will be inserting into the UserData table doesn't it matter if the names of the variables in the select match the names in the table definition. You are selecting 'name' to go into the UserData 'name' variable but you are selecting 'location' and somehow assigning it to the UserData 'oldlocation' variable. Will SQL just map these automatically or will it throw some kind of exception?

[CristiC]

It doesn't matter the name, only the column type.

[Aran Mulholland]

Wow that kind of makes sense but at the same time the parser in me feels kind of offended :)

[Paul-Sebastian]

I can't seem to be able to use this in UPDATE statement(s): gist link

[absmiths]

In an insert statement, if you don't declare the columns explicitly, then they are mapped in the order declared in the original create table statement, just like select * does. So, location in the select statement is mapped to oldlocation in the @userData table because location is in position 2 in the result set of the select, and oldlocation is column 2 in the table definition. That said, don't ever do this. Database ordering of columns or rows is not to be relied upon. Always be explicit about this.

[Mike Flynn]

I get MyTable is "Invalid Object Name" doing this, so there is something missing from this answer.

[AakashM]

@MikeFlynn MyTable here is a placeholder for the name of your actual table. I don't think there are any real databases with a table named MyTable ...

[Alicia]

And if I want to create/declare a table variable with SELECT INTO...? For example, to define the table variable's columns as t1.somecolumn, t1.othercolumn, t2.*

[atreeon]

I don't think this makes a copy, if you delete or update from userData will it not delete and update the records in your original tables?

[atreeon]

I get a 'Must declare the table variable "@TempWithheldTable " compile error message

[nanestev]

Yes, DELETE and UPDATE on the CTE will modify the source table as long as the CTE doesn't reference multiple tables using joins, unions , etc.

[Tony]

The downside to this is that you can only use the CTE table in the immediately following commands. If you need to make more than one pass through the result set for whatever reason CTE will not work. The OP seems to imply that multiple modifications will be made, in which case this won't work - "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)."

[Jamie Marshall]

You are confusing connection with session. Temp tables are scoped to session

[sǝɯɐſ]

not if you don't declare the table variable first, which is what I was trying to get around: i.imgur.com/2PXIubF.png

[Max Hay]

Ah, but that's what I want to do! I want to make a table variable from values in another table, so I don't have to specify the columns of the table 3 times. Once in the create table, and twice in the insert.

[AakashM]

@MaxHay sounds like you may want to ask a new question ...

[mzuther]

The real downside of using CTEs appears when you join CTEs that select from big tables. By using table variables instead of CTEs, I was able to cut down processing time by more than two orders of magnitude (source: 5 million rows, gain: >30 min to 5 sec)!