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.

Allisan

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;

Neat

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? – Putto

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

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

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

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

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

Winze

Really wish the accepted answer included this info! – Feoff

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

@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 ... – Winze

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.* – Epiblast

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

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

Answer 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

Toneless

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? – Breechloader

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

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)." – Convolute

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)! – Assiduity

Answer 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

Olsewski

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

Creating a temp has a little more overhead. – Leandraleandre

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

@Bess - 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. – Aspergillus

@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. :-) – Rodrigues

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

Answer 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

Ruhl

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

Answer 6

First create a temp table :

Step 1:

create table #tblOm_Temp (

    Name varchar(100),
    Age Int ,
    RollNumber bigint
)

**Step 2: ** Insert Some value in Temp table .

insert into #tblom_temp values('Om Pandey',102,1347)

Step 3: Declare a table Variable to hold temp table data.

declare   @tblOm_Variable table(

    Name Varchar(100),
    Age int,
    RollNumber bigint
)

Step 4: select value from temp table and insert into table variable.

insert into @tblOm_Variable select * from #tblom_temp

Finally value is inserted from a temp table to Table variable

Step 5: Can Check inserted value in table variable.

select * from @tblOm_Variable

Yellowgreen

Answer 7

OK, Now with enough effort i am able to insert into @table using the below :

INSERT @TempWithheldTable SELECT
a.SuspendedReason, a.SuspendedNotes, a.SuspendedBy , a.ReasonCode FROM OPENROWSET( BULK 'C:\DataBases\WithHeld.csv', FORMATFILE = N'C:\DataBases\Format.txt',
ERRORFILE=N'C:\Temp\MovieLensRatings.txt' ) AS a;

The main thing here is selecting columns to insert .

Adda

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

Answer 8

One reason to use SELECT INTO is that it allows you to use IDENTITY:

SELECT IDENTITY(INT,1,1) AS Id, name
INTO #MyTable 
FROM (SELECT name FROM AnotherTable) AS t

This would not work with a table variable, which is too bad...

Ribwort

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