Inserting data dynamically in Temporary table by Select Statement only

Question

I want to insert dynamic record in temporary table. I don't want to declare this table. I want its columns to be defined at run time. I am doing this because definition of table "Contract" may change in future. So temporary table(#x) should be changed accordingly. And this will be in a Stored Procedure.

I have tried following ways(which were marked as answers, none works though)

    EXECUTE IMMEDIATE 
    'SELECT top 1 * FROM Contract'
      INTO #x
    select * from #x

    declare @arj varchar(100)
    set @arj= 'SELECT top 1 * FROM Contract'
    select * into #x from 
    (SELECT top 1 * FROM Contract)

    declare @arj varchar(100)
    set @arj= 'SELECT top 1 * FROM Contract'
    SELECT * into #x execute ('execute' + 
    --and this-- SELECT into #T1 execute ('execute ' + @SQLString ) @arj  )
    SELECT *from #x

score 0 · Answer 1 · answered Jul 28 '14 at 07:25

0

You can use

SELECT top 1 * INTO #tempTable FROM Contract WHERE <any conditions you might want to filter the data>

answered Jul 28 '14 at 07:25

Jay

1,980
1
13
23

Hi Jayachandran, Actually my original query is pretty longer than this. The one I have mentioned is for reference. So it has to be in a variable. So, this one's not what I am looking for. – Arjun Jul 28 '14 at 08:09

score 0 · Answer 2 · answered Jul 28 '14 at 08:58

0

select *  
INTO #tempTable
from Contract  
WHERE 1=0

I Use this query to build a table with all the field of Contract table and the same properties. Hope it helps.

answered Jul 28 '14 at 08:58

CiucaS

2,010
5
36
63

My real query is longer than this. This variable @arj is for reference. – Arjun Jul 28 '14 at 09:31
I have long queries too instead of Contract use that ever you have in your query after FROM . – CiucaS Jul 28 '14 at 09:39
No sir, it's better if we keep @arj in the game. – Arjun Jul 28 '14 at 09:44

score 0 · Answer 3 · answered Jul 28 '14 at 09:19

0

this syntax should work.

declare @arj varchar(1000)
set @arj= 'SELECT top 1 * FROM Contract'
declare @charIdex int   
declare @arg2 varchar(1000)


SELECT @charIdex =  CHARINDEX ( 'FROM',
       @arj)
   SELECT @arg2 = STUFF(@arj, @charIdex, 0 , 'into tempTable ');
 exec   ( @arg2)
 select * from tempTable

answered Jul 28 '14 at 09:19

Asha hegde

26
4

Thank you for the reply but with this i get this error `Invalid object name '#tempTable'.` – Arjun Jul 28 '14 at 09:33
Just the same error I got for the other solutions i mentioned in my questions. – Arjun Jul 28 '14 at 09:34

Jayvee · Accepted Answer · 2014-07-28T08:54:31.790

-1

This syntax should work but you need to create the sql dynamically and then execute it:

 SELECT top 1  * 
 INTO #x
 FROM Contract

Example:

  SELECT TOP 0 * INTO #x from Contract 

  DECLARE @ARJ NVARCHAR(MAX)
  SET @ARJ='INSERT INTO #x SELECT TOP 1 * FROM Contract'
  EXECUTE sp_executesql @ARJ

edited Jul 28 '14 at 08:54

answered Jul 28 '14 at 07:18

Jayvee

10,670
3
29
40

Hi Jayvee, I get following message. `(1 row(s) affected)` `Invalid object name '#x'.` So it's not working for me. – Arjun Jul 28 '14 at 08:14
I've edited to use ##x instead of #x. Please try it now. – Jayvee Jul 28 '14 at 08:18
It works but that I cannot use. No global temp tables. – Arjun Jul 28 '14 at 08:44
1

The only work around in that case is to create the temp table before the sp and then insert into it. I edited my answer to reflect this. – Jayvee Jul 28 '14 at 08:55
Thank you Jayvee. However I get this error `An explicit value for the identity column in table '#x' can only be specified when a column list is used and IDENTITY_INSERT is ON.` But now I know what to do. Thanks. You gave me the direction to head. – Arjun Jul 28 '14 at 09:25

Inserting data dynamically in Temporary table by Select Statement only

4 Answers4