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

User · Answer

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

User · Answer

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

User · Answer

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 gt 30   SELECT    FROM   userData -- you can also reuse the recordset in subqueries and joins

User · Answer

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

User · Answer

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  gt  30

User · Answer

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

User · Answer

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

User · Answer

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

[sql-server] SELECT INTO a table variable in T-SQL

Examples related to sql-server

Examples related to tsql

Examples related to insert

Examples related to table-variable