CODEWITHSUNDEEP
sql-server
LaMar
LaMar

Reputation: 11

having problems with joining to a temp table

I am trying to join 3 tables into a temp table but keep getting this error:

Msg 2714, Level 16, State 6, Line 1
There is already an object named '#SalesOrder_History' in the database.

Here's what I'm trying:

CREATE TABLE #SalesOrder_History ([OrderDate] date, [DonutOrder_ID] int, [Customer_ID] int, [FirstName] VARCHAR(15), [LastName] VARCHAR(20), [StreetAddress] VARCHAR(20), [Apt] CHAR(3), [City] VARCHAR(15), [State] VARCHAR(15), [ZipCode] CHAR(5), [HomePhone] CHAR(10), [MobilePhone] CHAR(10), [OtherPhone] CHAR(10), [Qty] CHAR(3), [Donut_ID] INT, [DonutName] VARCHAR(10), [Description] VARCHAR(20), [UnitPrice] CHAR(5))
    SELECT Donut_Order.OrderDate, Donut_Order.DonutOrder_Id, Donut_Order.Customer_ID
    From Donut_Order
    FULL OUTER JOIN #SalesOrder_History AS SOH 
    ON Donut_Order.OrderDate=SOH.OrderDate
    FULL OUTER JOIN #SalesOrder_History AS SOH1
    ON Donut_Order.DonutOrder_Id=SOH1.DonutOrder_ID
    FULL OUTER JOIN #SalesOrder_History AS SOH13
    ON Donut_Order.Customer_ID=SOH13.Customer_ID

    UNION 
    Select Customers.FirstName, Customers.LastName, Customers.ApartmentNumber
    From Customers
    FULL OUTER JOIN #SalesOrder_History AS SOH2
    ON Customers.LastName=SOH2.LastName
    FULL OUTER JOIN #SalesOrder_History AS SOH12
    ON Customers.StreetName=SOH12.StreetAddress
    Full OUTER JOIN #SalesOrder_History AS SOH13
    ON Customers.ApartmentNumber=SOH13.Apt

    UNION 
    Select Customers.City, Customers.StateName, Customers.Zipcode
    From Customers
    FULL OUTER JOIN #SalesOrder_History AS SOH14
    ON Customers.City=SOH14.City
    FULL OUTER JOIN #SalesOrder_History AS SOH15
    ON Customers.StateName=SOH15.State
    FULL OUTER JOIN #SalesOrder_History AS SOH16
    ON Customers.Zipcode=SOH16.ZipCode

UNION 
    Select Customers.HomePhone, Customers.MobilePhone, Customers.OtherPhone 
    FROM Customers
    FULL OUTER JOIN #SalesOrder_History AS SOH17
    ON Customers.HomePhone=SOH17.HomePhone
    FULL OUTER JOIN #SalesOrder_History AS SOH18
    ON Customers.MobilePhone=SOH18.MobilePhone
    FULL OUTER JOIN #SalesOrder_History AS SOH19
    ON Customers.OtherPhone=SOH19.OtherPhone

    UNION 
    Select Donuts.Quanity, Donuts.Donut_Id, Donuts.DonutName
From Donuts
FULL OUTER JOIN #SalesOrder_History AS SOH20
    ON Donuts.Quanity=SOH20.Qty
FULL OUTER JOIN #SalesOrder_History AS SOH21
    ON Donuts.Donut_Id=SOH21.Donut_ID
FULL OUTER JOIN #SalesOrder_History AS SOH22
    ON Donuts.DonutName=SOH22.DonutName

UNION
Select Donuts.Donut_Description, Donuts.Price
From Donuts
FULL OUTER JOIN #SalesOrder_History AS SOH23
ON Donuts.Donut_Description=SOH23.Description
FULL OUTER JOIN #SalesOrder_History AS SOH24
ON Donuts.Price=SOH24.UnitPrice;

Upvotes: 1

Views: 33

Answers (1)

SqlZim
SqlZim

Reputation: 38023

add this to the beginning of your statement:

if object_id(N'tempdb..#SalesOrder_History') is not null drop table #SalesOrder_History;

If you have been fiddling with this over multiple tabs (sessions) in SSMS, you might have created the temporary table in a different tab (session) and will need to close that tab or drop the table from that tab.

Upvotes: 4

Related Questions