TSQL select into Temp table from dynamic sql

Question

This seems relatively simple  but apparently it s not   I need to create a temp table based on an existing table via the select into syntax   SELECT   INTO  TEMPTABLE FROM EXISTING TABLE   The problem is  the existing table name is accepted via a parameter     I can get the table s data via   execute   SELECT   FROM      tableName    but how do I marry the two so that I can put the results from the execute directly into the temp table   The columns for each table that this is going to be used for are not the same so building the temp table before getting the data is not practical   I m open to any suggestions except using a global temp table   Update   This is completely ridiculous  BUT my reservations with the global temp table is that this is a multi user platform lends itself to issues if the table will linger for long periods of time     Sooo   just to get past this part I ve started by using the execute to generate a global temp table   execute  select   into   globalDynamicFormTable from      tsFormTable     I then use the global temp table to load the local temp table   select   into  tempTable from   globalDynamicFormTable   I then drop the global table   drop table   globalDynamicFormTable   this is dirty and I don t like it  but for the time being  until i get a better solution  its going to have to work   In the End   I guess there is no way to get around it     The best answer appears to be either   Create a view in the execute command and use that to load the local temp table in the stored procedure   Create a global temp table in the execute command and use that to load the local temp table     With that said i ll probably just stick with the global temp table because creating and dropping views is audited in my organization  and I m sure they are going to question that if it starts happening all the time   Thanks

User · Answer

DECLARE  count ser temp int  DECLARE  TableName AS VARCHAR 100  SELECT  TableName    TableTemporal   EXECUTE   CREATE VIEW vTemp AS     SELECT       FROM      TableTemporal  SELECT TOP 1   INTO  servicios temp  FROM vTemp  DROP VIEW vTemp  -- Contar la cantidad de registros de la tabla temporal SELECT  count ser temp   COUNT    FROM  servicios temp   -- Recorro los registros de la tabla temporal  WHILE  count ser temp  gt  0  BEGIN  END END

User · Answer

declare  sql varchar 100    declare  tablename as varchar 100    select  tablename    your table name    create table  tmp       col1 int  col2 int  col3 int    set  sql    select aa  bb  cc from      tablename   insert into  tmp col1  col2  col3  exec   sql     select   from  tmp

User · Answer

How I did it with a pivot in dynamic sql   AccPurch was created prior to this   DECLARE  sql AS nvarchar MAX  declare  Month Nvarchar 1000   --DROP TABLE  temp select distinct YYYYMM into  temp from  AccPurch AS ap SELECT   Month   COALESCE  Month              CAST YYYYMM AS VarChar 8          FROM     temp  SELECT    Month  LEFT  Month len  Month -1    SET  sql   N SELECT UserID      Month   N  into   final Donovan 12345 FROM   Select ap AccPurch          ap YYYYMM          ap UserID          ap AccountNumber FROM  AccPurch AS ap    p Pivot  SUM AccPurch  FOR YYYYMM IN     Month  N    as pvt    EXEC sp executesql  sql  Select   INTO  final From   final Donovan 12345  DROP TABLE    final Donovan 12345  Select   From  final AS f

User · Answer

A working example   DECLARE  TableName AS VARCHAR 100  SELECT  TableName    YourTableName   EXECUTE   SELECT   INTO  TEMP  FROM      TableName     SELECT   FROM  TEMP      Second solution with accessible temp table   DECLARE  TableName AS VARCHAR 100  SELECT  TableName    YOUR TABLE NAME   EXECUTE   CREATE VIEW vTemp AS         SELECT           FROM      TableName  SELECT   INTO  TEMP  FROM vTemp   --DROP THE VIEW HERE       DROP VIEW vTemp    START USING TEMP TABLE                           --EX  SELECT   FROM  TEMP   --DROP YOUR TEMP TABLE HERE DROP TABLE  TEMP

User · Answer

Take a look at OPENROWSET  and do something like   SELECT   INTO  TEMPTABLE FROM OPENROWSET  SQLNCLI          Server  local  SQL2008 Trusted Connection yes          SELECT   FROM      tableName

[sql] TSQL select into Temp table from dynamic sql

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to temp-tables