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.

[To see links please register here]

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.

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.

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

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

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

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 .

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;

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

Try to use `INSERT` instead of `SELECT INTO`:

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

INSERT @UserData
SELECT name, oldlocation
```