CODEWITHSUNDEEP
sql-servert-sql
Gio
Gio

Reputation: 41

Inserting data into a temp table from an open query

I've looked online and you can not drop variables into an open query so easily so I modified my query and it says the #TempOT temp table is "Invalid object name '#TempOT'." What am I doing wrong that is causing the temp table not to get populated?

    BEGIN
IF OBJECT_ID('tempdb..#TempOT') IS NOT NULL
BEGIN
    DROP TABLE #TempOT
END;

 DECLARE @TSQL As varchar(1024)
 DECLARE @AS400Query As varchar(1024)
 SET @AS400Query = 'SELECT * from blah.blahlibrary.AS400Table where DATETS  BETWEEN '+ @BegofMonthForAS400 + ' AND ' + @DateForAS400 
 SET @TSQL = 'SELECT * FROM OPENQUERY(ISERIES,' + '''' + @AS400Query +'''' + ')'

INSERT INTO #TempOT
 EXEC(@TSQL)
END

i have also tried this and it does not work

    BEGIN
IF OBJECT_ID('tempdb..#TempOT') IS NOT NULL
BEGIN
    DROP TABLE #TempOT
END;

 DECLARE @TSQL As varchar(1024)
 DECLARE @AS400Query As varchar(1024)
 SET @AS400Query = 'SELECT * from valdosta.PRDATA.PRTIMELM where DATETS BETWEEN '+ @BegofMonthForAS400 + ' AND ' + @DateForAS400 
 SET @TSQL = ' INSERT INTO #TempOT SELECT * FROM OPENQUERY(ISERIES,' + '''' + @AS400Query +'''' + ')'


EXEC(@TSQL)

END

Upvotes: 1

Views: 2836

Answers (2)

Rokuto
Rokuto

Reputation: 814

The problem is in fact, that you are creating temporary table within EXEC statement. Created table #TempOT is dropped immediately after batch is executed` and is not visible for your main query. There are at least two possibilities (if you want to use temp tables):

  1. Instead of creating local temporary table, create global temporary table with ## before table name (difference between them is explained here). In my opinion, you should drop this table at the end of your main query.

  2. Create #TempOt with all columns before EXEC statement where you will be able without problem to populate table #TempOt with desired data.

Upvotes: 0

Ferdinand Gaspar
Ferdinand Gaspar

Reputation: 2063

You DROP the TEMP table and then you are trying to INSERT data into it. You need to create the TEMP table within your script first.

Upvotes: 2

Related Questions