Fixing Temp Table inside dynamic query

I had this question after viewing Create table dynamically while passing the table name.

Hi Experts,

How to fix this error in below query:
Invalid object name '#temp1'.


DECLARE @FindDuplicatedRowsInAsIsTableSQL varchar (MAX);
SET @FindDuplicatedRowsInAsIsTableSQL = N'SELECT
	 ID
	,Name
	,COUNT(*) MYCount
INTO #temp1
FROM MyTable
GROUP BY
	ID
	,Name
HAVING COUNT(*)>1'
EXECUTE(@FindDuplicatedRowsInAsIsTableSQL)

---Insert Duplicated Rows in AsIs_DupicatedRowsTable
DECLARE @InsertDuplicatedRowsIn_AsIsDupicatedRowsTableSQL varchar (MAX);
SET @InsertDuplicatedRowsIn_AsIsDupicatedRowsTableSQL = N'INSERT INTO Xtable
SELECT * 
FROM #temp1';
EXECUTE(@InsertDuplicatedRowsIn_AsIsDupicatedRowsTableSQL)

Open in new window




Thanks a lot in advance.
Harreni
HarreniAsked:
Who is Participating?
 
Nakul VachhrajaniConnect With a Mentor Technical Architect, Capgemini IndiaCommented:
The problem here is the scope of the temporary table. Since it is a local temporary table, it effectively goes out of scope when the EXECUTE on the dynamic SQL completes.

To fix this you need to either convert it to a global temporary table, or change the implementation to something similar to the following (basically I try to group the operations in a single query):

DECLARE @FindDuplicatedRowsInAsIsTableSQL varchar (MAX);
SET @FindDuplicatedRowsInAsIsTableSQL = N'SELECT
	 ID
	,Name
	,COUNT(*) MYCount
INTO #temp1
FROM MyTable
GROUP BY
	ID
	,Name
HAVING COUNT(*)>1

INSERT INTO Xtable
SELECT ID, Name, MyCount
FROM #temp1
'
EXECUTE(@FindDuplicatedRowsInAsIsTableSQL)

SELECT * FROM dbo.Xtable;

Open in new window

0
 
Pawan KumarConnect With a Mentor Database ExpertCommented:
You dont need temp table. Why complicate the process. Use below.

DECLARE @FindDuplicatedRowsInAsIsTableSQL varchar (MAX) = N'
INSERT INTO Xtable
SELECT
	 ID
	,Name
	,COUNT(*) MYCount
FROM MyTable
GROUP BY
	ID
	,Name
HAVING COUNT(*)>1'
EXECUTE(@FindDuplicatedRowsInAsIsTableSQL)

SELECT * FROM dbo.Xtable;

Open in new window



Even you dont need the dynamic string also.

INSERT INTO Xtable
SELECT
	 ID
	,Name
	,COUNT(*) MYCount
FROM MyTable
GROUP BY
	ID
	,Name
HAVING COUNT(*)>1

SELECT * FROM dbo.Xtable;

Open in new window


Enjoy !!
0
 
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
Without going into specifics, why are you creating temp tables using Dynamic SQL?   That's about as dangerous as the Danger Committee, mostly because as Nakul said the scope of the temp table begins when the statement is executed. Seems like a better way is possible such as a CTE.
0
 
HarreniAuthor Commented:
Thanks a lot Experts.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.