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

Question

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.

If you just want to retrieve data from one server and save it into database in another server, you can use `linked server`. Refer [**this**](http://stackoverflow.com/questions/1144051/selecting-data-from-two-different-servers-in-sql-server/22342227#22342227) — Raging Bull, Jun 15 '15 at 07:02
It's already linked .. My issue here is how to store my queried result into another table .. — Anaiah, Jun 15 '15 at 07:03
@Anaiah your last edits show that the problem is due to your tables definitions. It seems like your destination table doesn't correspond to the tables from which data are being queried. — Bellash, Jun 15 '15 at 07:22

Bellash · Accepted Answer · 2015-06-15T07:29:24.510

3

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.

edited Jun 15 '15 at 07:29

answered Jun 15 '15 at 07:04

Bellash

7,560
6
53
86

Then ? I would declare the temp table first ? or create it ? – Anaiah Jun 15 '15 at 07:09
Dear @Anaiah , `select into` will automatically create `tempTable` will all columns and let it available for use. – Bellash Jun 15 '15 at 07:11
I get `Incorrect syntax near the keyword 'INTO'.` in `SELECT INTO tempTable` – Anaiah Jun 15 '15 at 07:14
put `into` just before `from`. See how I edited my answer – Bellash Jun 15 '15 at 07:19
`An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Add a name or single space as the alias name. ` This is what i get again – Anaiah Jun 15 '15 at 07:23
@Anaiah: You have a computed column, `b.[PiecePrice] * b.[PartQuantity]`, which you have to give an alias, like so `b.[PiecePrice] * b.[PartQuantity] AS [ColumnName]` – Yannick Meeus Jun 15 '15 at 07:26
Thank you @YannickMeeus this helped me to edit my answer once more. – Bellash Jun 15 '15 at 07:32
@Anaiah please accept the answer if it solved your issue – Bellash Jun 15 '15 at 07:34

Giorgi Nakeuri · Answer 2 · 2015-06-15T07:06:42.533

2

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 ...

edited Jun 15 '15 at 07:06

answered Jun 15 '15 at 07:01

Giorgi Nakeuri

35,155
8
47
75

I tried to but I think i just don't how to use it .. Can u just atleast give me some idea how ? – Anaiah Jun 15 '15 at 07:02
What do you mean by `use it`? You can select from it `select * from #someName`, you can use it in joins etc like a regular table. – Giorgi Nakeuri Jun 15 '15 at 07:04
`Select * into #newtablename from myTable join someTable where ..` DId you mean like this ? – Anaiah Jun 15 '15 at 07:07
Yes, but #newtablename will be temp table that will be available only in current session. – Giorgi Nakeuri Jun 15 '15 at 07:10
`String or binary data would be truncated.` is what i get. – Anaiah Jun 15 '15 at 07:18
This error tells me nothing without some details. Where do you get this error. What is the statement? Etc – Giorgi Nakeuri Jun 15 '15 at 07:23

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

2 Answers2