SQL Store查询数据到一个表（临时或不临时）(SQL Store queried data to one table (either temporary or not))

我正在查询来自两个不同服务器的数据，现在我想将它存储在另一个表上，以便我可以将它用作我的程序中的参考表。（我在编程中使用ASP.NET）

看看我的命令，请告诉我该怎么做。

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()

这个查询的结果是我想存储在另一个表中，以便我可以使用它。

附加：

当我全部使用temp_tables时，我总是得到：

String or binary data would be truncated.

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.

最满意答案

如果您的表存在，您可以使用INSERT然后SELECT ，或者可以使用SELECT INTO来创建新表。

看到

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()

要么

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()

编辑。 ： select into会自动创建tempTable所有列并让它可供使用。

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.

更多推荐