I created a stored proc that creates a temp table, inserts, selects then drops. Executing the stored proc within SQL Server Management Studio works fine and gives the expected result.
CREATE PROCEDURE usp_TempTableTest
-- Add the parameters for the stored procedure here
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
CREATE TABLE #tmptable (
INSERT INTO #tmptable (color) VALUES (@color)
SELECT color FROM #tmptable
DROP TABLE #tmptable
However, when creating in the Import/Export tool and using that stored proc as the data source, it gives me the error:
Invalid object name '#tmptable'.
Any idea why this would happen? If I change it to a table variable it seems work fine with Import/Export, but I don't understand why it is not working with a temp table.
When I run a mimicked stored procedure, like yours above, in SSMS, I can get the data returned like you mentioned in the procedure. However, if I try the
#tmptable, like you did, I also get the same error because the
DROP TABLE removes it. From what I can tell, the import/export is basically a final
INSERT process. The reason it works with the table variable is because the data still exist on the final insert; in the case of the
DROP TABLE, it does not. For instance, when I remove the
DROP TABLE, it works.
I might be wrong here, but it seems the logic when it's an import or export in the case of the above procedure is
INSERT (import/export): this generates the "Invalid object name tmptable'"
With the variable (or no
In the second case, the data still exist. In the first case, they're gone. One way around it if you want to use the #tmptable, start your code with:
IF OBJECT_ID('tempdb..#tmptable') IS NOT NULL DROP TABLE #tmptable
Put "SET FMTONLY OFF;" right above "SET NOCOUNT ON"