CODEWITHSUNDEEP
sqlsql-server-2012
tchony papa
tchony papa

Reputation: 21

create a temp table using union

how to create a temp table using a union operator

select top 5 Name into [#Production.Productmodel]
from          [Production].[ProductModel]
select top 5 Name into [#purchasing.shipmethod]
from          [Purchasing].[ShipMethod]
select Name into #productionprod
select  Name from [#purchasing.shipmethod]
union
select  Name from [#Production.Productmodel]

but i still getting an error(invalid column name)

Upvotes: 1

Views: 19837

Answers (2)

jpw
jpw

Reputation: 44891

The reason your query fails is that you are repeating the SELECT NAMEin the query, so remove the second one:

select Name 
into #productionprod
from [#purchasing.shipmethod]
union
select Name from [#Production.Productmodel]

You could also have used two subqueries like this:

select Name into #productionprod
from (
  select Name from [#purchasing.shipmethod]
  union
  select Name from [#Production.Productmodel]
) subquery

On a side note: if the only reason you are first selecting into [#Production.Productmodel] and[#purchasing.shipmethod]is to use those temp tables as a source from SELECT ... INTO #productionprod then your query could be simplified to this:

SELECT Name INTO #productionprod
FROM (
    SELECT TOP 5 Name 
    FROM  [Production].[ProductModel] ORDER BY name
    UNION
    SELECT TOP 5 Name 
    FROM  [Production].[ShipMethod] ORDER BY name
) a

Please note that when you useTOP nwithoutORDER BYthe order is undefined as stated in the documentation:

When TOP is used in conjunction with the ORDER BY clause, the result set is limited to the first N number of ordered rows; otherwise, it returns the first N number of rows in an undefined order.

Upvotes: 4

Gordon Linoff
Gordon Linoff

Reputation: 1269913

You can use into. Here is an example:

select Name
into #productionprod
from [#purchasing.shipmethod]
union
select Name
from [#Production.Productmodel]

Upvotes: 2

Related Questions