Insert into temp table from exec call stored procedure

Question

I have a stored procedure where I insert a table into a temporary table and then I read that temp table row by row using a cursor:

USE [TEST_DB]
GO    
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[returnValidationFailures] 

AS
BEGIN

    SET NOCOUNT ON;

    SELECT TOP 0 * INTO  TempTbl FROM   USER

    DECLARE cursor1 CURSOR FAST_FORWARD FOR
        SELECT *
        FROM   USER

    OPEN cursor1

    INSERT INTO TempTbl 
    EXEC ('fetch next from cursor1')

    WHILE @@FETCH_STATUS = 0
        INSERT INTO TempTbl 
        EXEC ('fetch next from cursor1')

    CLOSE cursor1

    DEALLOCATE cursor1

    SELECT *
    FROM   TempTbl 

    DROP TABLE TempTbl 

END

What I want here is to send the table name through a paramater like:

@TableNameParam varchar(10)

And then insert into temp table like:

SELECT TOP 0 * INTO  TempTbl FROM  @TableNameParam
DECLARE cursor1 CURSOR FAST_FORWARD FOR
        SELECT *
        FROM   @TableNameParam

This doesn't work (obviously). But every other method I tried didn't work. Is there any way I can set the data of an EXEC call like:

EXEC ('SELECT * FROM ' + @TableNameParam
    ' WHERE STATUS=1')

into the temp table?

Note: I DO NOT know the table structure.

Do you need to send a table as parameter or to get the result of a stored procedure into a temporary table or both?\ — bjnr, Apr 08 '14 at 06:44
@MihaiBejenariu , I'm sending the table name as a parameter to the stored procedure. The stored procedure should read the data from that table and insert it into a temp table — Dilanga Thalpegama, Apr 08 '14 at 06:47
@NenadZivkovic , I'm using a cursor here because I need to process the data row by row. I have not included any of that code here. — Dilanga Thalpegama, Apr 08 '14 at 06:48

bjnr · Answer 1 · 2014-04-08T06:56:59.303

0

I'm not very sure I understand what is your final scope, but my advise is to use a set-based approach.

To respond directly to you question, you should take into account the usage of temporary tables (#TempTable) instead of table variables (@TempTable).

EXEC ('SELECT * FROM ' + #TableNameParam + ' WHERE STATUS=1')

How is your data obtained in your temporary table?

edited Apr 08 '14 at 06:56

answered Apr 08 '14 at 06:38

bjnr

3,353
1
18
32

what I need is something like this (pseudo code): `INSERT INTO TempTbl EXEC ('SELECT * FROM ' + @TableNameParam + ' WHERE STATUS=1')` where `@TableNameParam` is only the name of the original table from which I get the data – Dilanga Thalpegama Apr 08 '14 at 07:47
What about: INSERT INTO #TempTable EXEC returnValidationFailures; DELETE FROM #TempTable WHERE status != 1 – bjnr Apr 08 '14 at 07:54
I can't use that. To use that, I have to define the temp table previously. As the temp table should be dynamic, I cannot use that. What I need is a replacement for: `SELECT TOP 0 * INTO TempTbl FROM USER`. This line defines the table AND inserts data into it\ – Dilanga Thalpegama Apr 08 '14 at 08:01
@DilangaThalpegama `EXEC ('SELECT TOP 0 * INTO TempTbl FROM ' +@TableNameParam)` ? – Nenad Zivkovic Apr 08 '14 at 08:35

score 0 · Answer 2 · answered Aug 14 '23 at 11:28

You can use dynamic sql. https://www.sqlshack.com/dynamic-sql-in-sql-server/

You may also need to use a global temp table like ##TempTbl.

Below is an example using your code (warning I've not tested):

USE [TEST_DB]
GO    
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[returnValidationFailures] @TableNameParam varchar(10)

AS
BEGIN

SET NOCOUNT ON;

DECLARE @SQL nvarchar(1000)

SET @SQL = '

SELECT TOP 0 * INTO ##TempTbl FROM ' + @TableNameParam + '

DECLARE cursor1 CURSOR FAST_FORWARD FOR
    SELECT *
    FROM ' + @TableNameParam + '

OPEN cursor1

INSERT INTO ##TempTbl 
EXEC (''fetch next from cursor1'')

WHILE @@FETCH_STATUS = 0
    INSERT INTO ##TempTbl
    EXEC (''fetch next from cursor1'')

CLOSE cursor1

DEALLOCATE cursor1

SELECT *
FROM ##TempTbl

DROP TABLE ##TempTbl
'

EXEC(@SQL)

END

Hope this helps!

Insert into temp table from exec call stored procedure

2 Answers2