444

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.

iamdave
  • 12,023
  • 3
  • 24
  • 53
Indrek
  • 6,516
  • 4
  • 29
  • 27

8 Answers8

680

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;
CristiC
  • 22,068
  • 12
  • 57
  • 89
  • 2
    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? – Aran Mulholland Nov 17 '15 at 07:23
  • It doesn't matter the name, only the column type. – CristiC Nov 18 '15 at 04:26
  • 5
    Wow that kind of makes sense but at the same time the parser in me feels kind of offended :) – Aran Mulholland Nov 18 '15 at 04:28
  • I can't seem to be able to use this in UPDATE statement(s): [gist link](https://gist.github.com/brokenthorn/7452832c97f1d1d003cd5a66295f470f#file-example-sql) – Paul-Sebastian Manole Nov 25 '16 at 12:32
  • 2
    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. – absmiths Jul 14 '17 at 21:33
109

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.

AakashM
  • 62,551
  • 17
  • 151
  • 186
  • 2
    Really wish the accepted answer included this info! – Davie Brown Sep 16 '15 at 12:45
  • I get MyTable is "Invalid Object Name" doing this, so there is something missing from this answer. – Mike Flynn Aug 11 '17 at 13:45
  • @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` ... – AakashM Aug 14 '17 at 07:30
  • 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.* – Alicia Mar 07 '18 at 19:27
  • 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. – Max Hay Feb 25 '22 at 10:41
  • @MaxHay sounds like you may want to [ask a new question](https://stackoverflow.com/questions/ask) ... – AakashM Mar 01 '22 at 14:43
33

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
nanestev
  • 822
  • 8
  • 15
  • 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 Apr 12 '18 at 15:02
  • 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. – nanestev Apr 13 '18 at 14:11
  • 5
    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)." – Tony Jun 05 '18 at 02:05
  • 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)! – mzuther Mar 10 '22 at 11:46
20

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

Whimsical
  • 5,985
  • 1
  • 31
  • 39
  • 3
    Sorry, forgot to mention I don't have rights for CREATE TABLE. – Indrek Oct 01 '10 at 10:23
  • 7
    Creating a temp has a little more overhead. – paparazzo Dec 04 '12 at 17:07
  • 2
    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. – Franck Mar 05 '14 at 13:43
  • 14
    @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. – maf748 Oct 23 '14 at 13:53
  • @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. :-) – underscore_d Aug 26 '15 at 13:19
  • You are confusing `connection` with `session`. Temp tables are scoped to `session` – Jamie Marshall Jan 26 '19 at 18:16
13

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
Zephyr was a Friend of Mine
  • 7,661
  • 4
  • 37
  • 39
  • not if you don't declare the table variable first, which is what I was trying to get around: https://i.imgur.com/2PXIubF.png – sǝɯɐſ Dec 21 '20 at 16:40
7

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
Nerdroid
  • 13,398
  • 5
  • 58
  • 69
404 Not found
  • 201
  • 3
  • 7
1

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 .

RahulJha
  • 63
  • 8
-5

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

Smern
  • 18,746
  • 21
  • 72
  • 90
MOHCTP
  • 1
  • 3