Error Message on Export: "There is already an object named '#*****' in the database"
I'm trying to get a quick export of data for a website for easy local use and analysis in Excel and I'm getting stuck with the export function on FlySpeed Version 220.127.116.11.
I'm running the following query,
SET NOCOUNT ON
C.id, C.type, C.created, COUNT(A.id) AS applicants, CS.status, ISNULL(B.id, 0) AS brokerid, B.name AS broker
dbo.applicant A INNER JOIN
dbo.[case] C INNER JOIN dbo.case_applicant CA ON C.id = CA.case_id
ON A.id = CA.applicant_id
INNER JOIN dbo.case_status CS ON C.status_id = CS.id
LEFT OUTER JOIN dbo.broker B ON C.broker_id = B.id
GROUP BY C.id, C.type, C.created, CS.status, B.id, B.name
SELECT C.id, MIN(A.id) AS applicant_id
FROM dbo.applicant A INNER JOIN dbo.case_applicant CA
INNER JOIN #rob C ON C.id = CA.case_id
ON CA.applicant_id = A.id
GROUP BY C.id
SET NOCOUNT OFF
SELECT C.id, C.type, C.created, C.applicants, A.title + ' ' + A.firstname + ' ' + A.lastname AS applicant1, C.status, C.brokerid, C.broker
FROM #rob C INNER JOIN #rob2 I
INNER JOIN dbo.applicant A ON I.applicant_id = A.id
ON C.id = I.id
That returns all the results I need fine but when I try to export, I get the following eror message pop up,
"There is already an object named '#rob' in the database"
Any ideas of how to get round this?
Thanks in advance.
March 23 2012, 08:25 AM
It is not possible to export data from temporary tables using this tool. Use derived tables or common table expressions instead:
WITH Rob AS (SELECT ...),
Rob2 AS (SELECT ...)
SELECT ... FROM Rob Inner Join Rob2 ...
3 days, 20 hours since original post