CODEWITHSUNDEEP
sqlsql-servermultiple-tables
Noman Khan
Noman Khan

Reputation: 23

how to create temp table in sql server with result set of multiple joined table

For example-I want to create temp table in sql server with result of this query as I have tried query below but,I have found 'The column 'EmployeeID' was specified multiple times for 'ResultSet` Please help.thanks

SELECT * 
INTO #TempTable
FROM 
(
SELECT 
     Employees.EmployeeID
    ,Employees.LastName
    ,Employees.FirstName
    ,Employees.Title
    ,Employees.TitleOfCourtesy
    ,Employees.BirthDate
    ,Employees.HireDate
    ,Employees.Address
    ,Employees.City
    ,Employees.Region
    ,Employees.PostalCode
    ,Employees.Country
    ,Employees.HomePhone
    ,Employees.Extension
    ,Employees.Photo
    ,Employees.Notes
    ,Employees.ReportsTo
    ,Employees.PhotoPath
    ,Orders.OrderID
    ,Orders.CustomerID
    ,Orders.EmployeeID
    ,Orders.OrderDate
    ,Orders.RequiredDate
    ,Orders.ShippedDate
    ,Orders.ShipVia
    ,Orders.Freight
    ,Orders.ShipName
    ,Orders.ShipAddress
    ,Orders.ShipCity
    ,Orders.ShipRegion
    ,Orders.ShipPostalCode
    ,Orders.ShipCountry
    ,Shippers.ShipperID
    ,Shippers.CompanyName
    ,Shippers.Phone
    ,[Order Details].OrderID
    ,[Order Details].ProductID
    ,[Order Details].UnitPrice
    ,[Order Details].Quantity
    ,[Order Details].Discount
    ,Products.ProductID
    ,Products.ProductName
    ,Products.SupplierID
    ,Products.CategoryID
    ,Products.QuantityPerUnit
    ,Products.UnitPrice
    ,Products.UnitsInStock
    ,Products.UnitsOnOrder
    ,Products.ReorderLevel
    ,Products.Discontinued
    ,Categories.CategoryID
    ,Categories.CategoryName
    ,Categories.Description
    ,Categories.Picture
    ,Suppliers.SupplierID
    ,Suppliers.CompanyName
    ,Suppliers.ContactName
    ,Suppliers.ContactTitle
    ,Suppliers.Address
    ,Suppliers.City
    ,Suppliers.Region
    ,Suppliers.PostalCode
    ,Suppliers.Country
    ,Suppliers.Phone
    ,Suppliers.Fax
    ,Suppliers.HomePage
FROM ( ( ( ( ( ( ( Northwind.Dbo.Products Products
                                    INNER JOIN 
                                    Northwind.Dbo.Categories Categories
                                    ON (Products.CategoryID = Categories.CategoryID))
                                INNER JOIN 
                                Northwind.Dbo.[Order Details] [Order Details]
                                ON ([Order Details].ProductID = Products.ProductID))
                            INNER JOIN 
                            Northwind.Dbo.Orders Orders
                            ON ([Order Details].OrderID = Orders.OrderID))
                        INNER JOIN 
                        Northwind.Dbo.Customers Customers
                        ON (Orders.CustomerID = Customers.CustomerID))
                    INNER JOIN 
                    Northwind.Dbo.Employees Employees
                    ON (Orders.EmployeeID = Employees.EmployeeID))
                INNER JOIN 
                Northwind.Dbo.EmployeeTerritories EmployeeTerritories
                ON (EmployeeTerritories.EmployeeID = Employees.EmployeeID))
            INNER JOIN 
            Northwind.Dbo.Shippers Shippers
            ON (Orders.ShipVia = Shippers.ShipperID))
        INNER JOIN 
        Northwind.Dbo.Suppliers Suppliers
        ON (Products.SupplierID = Suppliers.SupplierID) ) as ResultSet

Upvotes: 1

Views: 533

Answers (3)

Dinch
Dinch

Reputation: 612

Your temp table column names are going to be just column name. It is not going to include your table names. To answer your question, remove duplicated column names from your select statement or give an alias name.

SELECT 
     Employees.EmployeeID // this is enough
    ,Employees.LastName
    ,Employees.FirstName
    ,Employees.Title
    ,Employees.TitleOfCourtesy
    ,Employees.BirthDate
    ,Employees.HireDate
    ,Employees.Address
    ,Employees.City
    ,Employees.Region
    ,Employees.PostalCode
    ,Employees.Country
    ,Employees.HomePhone
    ,Employees.Extension
    ,Employees.Photo
    ,Employees.Notes
    ,Employees.ReportsTo
    ,Employees.PhotoPath
    ,Orders.OrderID
    ,Orders.CustomerID
    // remove this line. Or use alias
    ,Orders.EmployeeID AS [Orders_EmloyeeID]
    // ,Orders.EmployeeID
    ,Orders.OrderDate
    ,Orders.RequiredDate
    ,Orders.ShippedDate
    ,Orders.ShipVia

Upvotes: 1

Gurgen Sargsyan
Gurgen Sargsyan

Reputation: 1087

Change part Employees.EmployeeID as ID

Upvotes: 0

McNets
McNets

Reputation: 10807

EmployeeID appears twice in your select statement, use an alias or delete one of your columns.

SELECT 
     Employees.EmployeeID    <-------
    ,Employees.LastName
    ,Employees.FirstName
    ,Employees.Title
    ,Employees.TitleOfCourtesy
    ,Employees.BirthDate
    ,Employees.HireDate
    ,Employees.Address
    ,Employees.City
    ,Employees.Region
    ,Employees.PostalCode
    ,Employees.Country
    ,Employees.HomePhone
    ,Employees.Extension
    ,Employees.Photo
    ,Employees.Notes
    ,Employees.ReportsTo
    ,Employees.PhotoPath
    ,Orders.OrderID
    ,Orders.CustomerID
    ,Orders.EmployeeID      <-------

For example:

    ,Orders.EmployeeID as OrderEmployeeID

Upvotes: 1

Related Questions