CODEWITHSUNDEEP
c#sql-server
Anaiah
Anaiah

Reputation: 633

SQL Store queried data to one table (either temporary or not)

I am querying a data from two different servers and now I want to store it on another table so that I can use it as my reference table in my program.. (I am using ASP.NET in programming)

Have a look on my command and please advise what to do.

SELECT c.[pf_id]
      ,a.[RequestDate]
      ,c.[pf_carrierUsed]
      ,b.[PiecePrice] * b.[PartQuantity]
      ,c.[pf_type]
      ,c.[pf_resSupplier]
      ,c.[pf_resCustomer]
      ,c.[pf_trailerNum]
      ,b.[PartDesc]
      ,c.[pf_chargeBack]
      ,c.[pf_chargetoPlant]

FROM [CNCTC-WEB01].[NOP_PR].[dbo].[Requests] a
JOIN [CNCTC-WEB01].[NOP_PR].[dbo].[Parts] b on a.[RequestID] = b.[RequestID]
JOIN [PHRIZ-WEBAPP01].[PFTracking].[dbo].[Tbl_PFExcel] c on  b.[PartNumber] like '%'+c.pf_id+'%'
where a.[EntityName] like '%PTA' 
AND a.[RequestDate] between '2015-04-20 00:00:00.000' AND GETDATE()

The result of this query is what I wanted to store in another table so that I could use it.

Additional :

When I do all use temp_tables i always get :

String or binary data would be truncated.

Upvotes: 1

Views: 52

Answers (2)

Bellash
Bellash

Reputation: 8184

You can use INSERT followed by your SELECT if your table exists or you can use SELECT INTO in order to create the new table.

see

 INSERT INTO tempTable
 SELECT c.[pf_id]
  ,a.[RequestDate]
  ,c.[pf_carrierUsed]
  ,b.[PiecePrice] * b.[PartQuantity] AS totalPrice
  ,c.[pf_type]
  ,c.[pf_resSupplier]
  ,c.[pf_resCustomer]
  ,c.[pf_trailerNum]
  ,b.[PartDesc]
  ,c.[pf_chargeBack]
  ,c.[pf_chargetoPlant]

 FROM [CNCTC-WEB01].[NOP_PR].[dbo].[Requests] a
   JOIN [CNCTC-WEB01].[NOP_PR].[dbo].[Parts] b on a.[RequestID] =      b.[RequestID]
   JOIN [PHRIZ-WEBAPP01].[PFTracking].[dbo].[Tbl_PFExcel] c on  b.[PartNumber] like '%'+c.pf_id+'%'
   where a.[EntityName] like '%PTA' 
   AND a.[RequestDate] between '2015-04-20 00:00:00.000' AND GETDATE()

or 

 SELECT c.[pf_id]
  ,a.[RequestDate]
  ,c.[pf_carrierUsed]
  ,b.[PiecePrice] * b.[PartQuantity] As TotalPrice
  ,c.[pf_type]
  ,c.[pf_resSupplier]
  ,c.[pf_resCustomer]
  ,c.[pf_trailerNum]
  ,b.[PartDesc]
  ,c.[pf_chargeBack]
  ,c.[pf_chargetoPlant]
 INTO tempTable
 FROM [CNCTC-WEB01].[NOP_PR].[dbo].[Requests] a
   JOIN [CNCTC-WEB01].[NOP_PR].[dbo].[Parts] b on a.[RequestID] =      b.[RequestID]
   JOIN [PHRIZ-WEBAPP01].[PFTracking].[dbo].[Tbl_PFExcel] c on  b.[PartNumber] like '%'+c.pf_id+'%'
   where a.[EntityName] like '%PTA' 
   AND a.[RequestDate] between '2015-04-20 00:00:00.000' AND GETDATE()

EDITS.: select into will automatically create tempTable will all columns and let it available for use.

Upvotes: 3

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

You can create temp table on the fly and then reuse it:

select * into #someName
from someTable
join someOtherTable
...
where ...

If you already have a table then just insert select statement:

insert into alreadyCreatedTable
select *
from someTable
join someOtherTable
...
where ...

Upvotes: 2

Related Questions