SELECT INTO a table variable in T-SQL

sql server select into table variable without declaring
select into temp table
declare table variable sql
sql select into temp table
table variable in sql server w3schools
sql table variable vs temp table
temp table sql
select * from table variable sql server

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.

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;

SQL SERVER - SELECT INTO a Table Variable in T-SQL, The question was it is possible to do SELECT INTO a Table Variable in T-SQL? The answer is it is not possible at all. Let us understand what  You cannot specify a table variable or table-valued parameter as the new table. You cannot use SELECTINTO to create a partitioned table, even when the source table is partitioned. SELECTINTO does not use the partition scheme of the source table; instead, the new table is created in the default filegroup.

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.

Table Variables In T-SQL, While connected to the Northwind data-base, we could write the following SELECT statement to populate the table variable. Here you don't define the table being returned and the body of the function can only be one SELECT statement. At the moment your code is somewhere between the two; you need to get this to work as the first option, i.e. the Table-Valued User-Defined Function; start by defining the table being returned in the RETURNS clause and go from there.

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

An Introduction to SQL Server Table Variables By Examples, This tutorial shows you how to use the SQL Server table variables which offer some you can query data from the table variables using the SELECT statement​:. You cannot SELECT .. INTO .. a TABLE VARIABLE. The best you can do is create it first, then insert into it. Your 2nd snippet has to be. DECLARE @TempCustomer TABLE ( CustomerId uniqueidentifier, FirstName nvarchar (100), LastName nvarchar (100), Email nvarchar (100) ); INSERT INTO @TempCustomer SELECT CustomerId, FirstName, LastName, Email

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

INTO Clause (Transact-SQL), You cannot specify a table variable or table-valued parameter as the new table. You cannot use SELECTINTO to create a partitioned table,  The following SQL statement uses the IN clause to copy the table into a new table in another database: SELECT * INTO CustomersBackup2017 IN 'Backup.mdb' FROM Customers;

Try to use INSERT instead of SELECT INTO:

INSERT @UserData   
SELECT name, location etc.

SQL Server Table Variable Example, For this kind of application, it is best to follow an INSERT statement with a SELECT statement when inserting values into a table variable. This  SELECT @local_variable is typically used to return a single value into the variable. However, when expression is the name of a column, it can return multiple values. If the SELECT statement returns more than one value, the variable is assigned the last value that is returned.

The Table Variable in SQL Server, This article helps to learn the concept of the table variable in SQL Server. smallest logical unit that helps to manage the CRUD (insert, select,  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.

Selecting into Variables in Transact-SQL, Variables are essential to the operation of many stored procedures, as they allow the Selecting into Variables in Transact-SQL This table might be used in a company database to store the names of all employees and the  For example, the following statement declares a table variable named @product_table which consists of three columns: product_name, brand_id, and list_price: DECLARE @product_table TABLE ( product_name VARCHAR(MAX) NOT NULL, brand_id INT NOT NULL, list_price DEC(11,2) NOT NULL. Inserting data into the table variables.

Temporary Tables vs. Table Variables in SQL Server |, Learn when it is most appropriate to use a temporary table vs. when you 'On the fly' by designating an “INTO” clause after a SELECT statement and before a FROM clause: SQL Server using Temporary vs Variable Tables. 2. SQL Server Table Variables; table (Transact-SQL) SELECT INTO a table variable in T-SQL; Comparative Analysis of Table Variables on User Defined Functions, Indexes and Scope; Declare variable in table valued function; Drop Function if exist – SQL Server; GO After every T-SQL statement; Last Updated: 2019-05-20

Comments
  • 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?
  • It doesn't matter the name, only the column type.
  • Wow that kind of makes sense but at the same time the parser in me feels kind of offended :)
  • I can't seem to be able to use this in UPDATE statement(s): gist link
  • 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.
  • Really wish the accepted answer included this info!
  • I get MyTable is "Invalid Object Name" doing this, so there is something missing from this answer.
  • @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 ...
  • 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.*
  • Love this! Thank you.
  • 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?
  • 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.
  • 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)."
  • Sorry, forgot to mention I don't have rights for CREATE TABLE.