Câu hỏi Chèn kết quả của một thủ tục được lưu trữ vào một bảng tạm thời


Làm thế nào để tôi làm một SELECT * INTO [temp table] FROM [stored procedure]? không phải FROM [Table] và không xác định [temp table]?

Select tất cả dữ liệu từ BusinessLine vào tmpBusLine hoạt động tốt.

select *
into tmpBusLine
from BusinessLine

Tôi đang cố gắng giống nhau, nhưng sử dụng stored procedure trả về dữ liệu, không hoàn toàn giống nhau.

select *
into tmpBusLine
from
exec getBusinessLineHistory '16 Mar 2009'

Thông báo đầu ra:

Msg 156, Cấp 15, Tiểu bang 1, Dòng 2   Cú pháp không chính xác gần từ khóa   'exec'.

Tôi đã đọc một số ví dụ về việc tạo ra một bảng tạm thời với cùng một cấu trúc như thủ tục lưu trữ đầu ra, hoạt động tốt, nhưng nó sẽ là tốt đẹp để không cung cấp bất kỳ cột nào.


1340
2018-03-17 10:45


gốc


Với SELECT * INTO [TABLE NAME] bạn biết các cột, vì chúng được sao chép từ bảng gốc. Đây là chính xác những gì tôi muốn nếu tôi đã làm điều tương tự đối với một thủ tục được lưu trữ. - Ferdeen
Xem sommarskog.se/share_data.html và bài của tôi stackoverflow.com/questions/6215672/… - Triynko
Chỉ muốn chỉ ra rằng "select * into tmpBusLine" tạo một bảng vĩnh viễn. Bạn có thể muốn "chọn * vào #tmpBusLine". Tôi chắc chắn các poster ban đầu đã tìm thấy điều này nhưng nó có thể giúp những người khác tìm thấy bài đăng này vì nó là kết quả hàng đầu hiện tại cho tìm kiếm "chọn vào bảng tạm thời" - ktam33
Tôi không biết nếu điều này đã được giải quyết hay không nhưng lý do tại sao bạn nhận được lỗi là do từ khóa từ. - Wes Palmer
Bạn nên sử dụng OPENROWSET. - Yogesh Sharma


Các câu trả lời:


Bạn có thể dùng OPENROWSET cho điều này. Có một cái nhìn. Tôi cũng đã bao gồm mã sp_configure để kích hoạt Ad Hoc Distributed Queries, trong trường hợp nó chưa được kích hoạt.

CREATE PROC getBusinessLineHistory
AS
BEGIN
    SELECT * FROM sys.databases
END
GO

sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO

SELECT * INTO #MyTempTable FROM OPENROWSET('SQLNCLI', 'Server=(local)\SQL2008;Trusted_Connection=yes;',
     'EXEC getBusinessLineHistory')

SELECT * FROM #MyTempTable

621
2017-08-04 15:27



Đây là cách đúng để làm điều đó. OPENROWSET là cách duy nhất để xử lý các kết quả của một thủ tục được lưu trữ dưới dạng một biểu thức bảng. - Rob Farley
Điều này có vẻ hơi cồng kềnh chỉ để chèn vào một cái bàn. Rất nhiều cấu hình để làm. Ngoài ra khi tôi đã thử nó tôi đã nhận "Msg 7357, Cấp 16, Tiểu bang 2, Dòng 1 Không thể xử lý đối tượng" EXEC GetPartyAnalysisData 146 ". Nhà cung cấp OLE DB" SQLNCLI "cho máy chủ được liên kết" (null) "cho biết rằng đối tượng có không có cột nào hoặc người dùng hiện tại không có quyền đối với đối tượng đó. " Vì vậy, bạn cần phải đặt một máy chủ được liên kết ... - Ferdeen
Bạn không cần một máy chủ được liên kết, nhưng bạn sẽ cần phải nhận được chuỗi kết nối đúng ... và cũng có thể, chỉ định đường dẫn đầy đủ đến thủ tục được lưu trữ bao gồm tên cơ sở dữ liệu và chủ sở hữu của sp. - MartW
eeeeew! một tham chiếu đến cùng một máy chủ? bẩn thỉu. chắc chắn hơn một hack hơn là phải tự tạo bảng tạm thời - Tim Abell
tôi đồng ý rằng đây là một hack và có lẽ nên tránh trừ khi lưng của bạn là chống lại bức tường. Thay đổi sp thành một hàm có lẽ là một góc tốt hơn để thực hiện. IMHO. - greg


Nếu bạn muốn làm điều đó mà không khai báo bảng tạm thời, bạn có thể thử tạo một hàm do người dùng định nghĩa thay vì thủ tục lưu trữ và làm cho hàm do người dùng định nghĩa đó trả về một bảng. Thay thế, nếu bạn muốn sử dụng thủ tục được lưu trữ, hãy thử một cái gì đó như thế này:

CREATE TABLE #tmpBus
(
   COL1 INT,
   COL2 INT
)

INSERT INTO #tmpBus
Exec SpGetRecords 'Params'

518
2018-03-17 14:08



Tôi nghĩ rằng vấn đề là tạo lược đồ mà không cần khai báo rõ ràng. - Craig
Tôi muốn biết sự khác biệt giữa giải pháp này và giải pháp @Aaron Alton ở trên. Điều này có vẻ đơn giản hơn nhiều, nhưng tôi không chắc chắn về bất kỳ tác động nào khác. - funkymushroom
Điều này sẽ làm việc nhưng nếu bạn đã thêm các cột bổ sung vào thủ tục lưu trữ SpGetRecords, điều này sẽ tăng lên. - Brady
Bạn chỉ nhận được một INSERT INTO EXEC cho mỗi ngăn xếp cuộc gọi. SpGetRecords và bất kỳ proc nào khác mà nó gọi có thể không sử dụng chiến lược này trong mã riêng của họ. Điều này có thể gây bất ngờ cho người bảo trì SpGetRecords. - Matt Stephenson
Điều này không trả lời được câu hỏi nào cả và tôi không hiểu tại sao nó lại được bình chọn như vậy? OP đã tuyên bố rõ ràng "không xác định [bảng tạm thời]" và dòng đầu tiên của bạn có một câu lệnh bảng tạo tạm thời. - NickG


Trong SQL Server 2005, bạn có thể sử dụng INSERT INTO ... EXEC để chèn kết quả của một thủ tục được lưu trữ vào một bảng. Từ MSDN's INSERT tài liệu (đối với SQL Server 2000, trên thực tế):

--INSERT...EXECUTE procedure example
INSERT author_sales EXECUTE get_author_sales

268
2018-03-17 10:50



Điều này yêu cầu authors_sales được xác định trước. Tôi đang cố tránh điều này. Cảm ơn. - Ferdeen
Ah đúng rồi. Vâng, tôi nghĩ bạn có thể không may mắn trong trường hợp đó. - Matt Hamilton
Tôi nghĩ rằng càng nhiều. Vì vậy, hữu ích Chèn vào bảng tmp trên bay, nhưng không nên hữu ích nếu bạn cần phải biết cấu trúc tập dữ liệu trả về từ một proc được lưu trữ. Cảm ơn bạn đã giúp đỡ. - Ferdeen
Có một bài viết hay ở đây msdn.microsoft.com/en-us/library/aa175921.aspx - Rich Andrews
Để sử dụng cùng một lược đồ, bạn có thể tạo một bản sao như sau: chọn đầu 0 * vào tempTable từ realTable (stackoverflow.com/a/9206463/73794) - Even Mien


Đây là câu trả lời cho phiên bản được sửa đổi một chút của câu hỏi của bạn. Nếu bạn có thể từ bỏ việc sử dụng một thủ tục được lưu trữ cho một hàm do người dùng định nghĩa, bạn có thể sử dụng hàm nội suy do người dùng định nghĩa trong bảng. Về cơ bản, đây là một thủ tục được lưu trữ (sẽ lấy các tham số) trả về một bảng như là một tập kết quả; và do đó sẽ đặt độc đáo với tuyên bố INTO.

Đây là một điều tốt bài viết nhanh trên đó và các hàm do người dùng định nghĩa khác. Nếu bạn vẫn có nhu cầu lái xe cho một thủ tục được lưu trữ, bạn có thể bọc hàm nội tuyến do người dùng định nghĩa bằng một thủ tục được lưu trữ. Thủ tục được lưu trữ chỉ chuyển các tham số khi nó gọi select * từ hàm nội tuyến do người dùng định nghĩa.

Vì vậy, ví dụ, bạn sẽ có một hàm nội tuyến do người dùng định nghĩa có giá trị để lấy danh sách khách hàng cho một vùng cụ thể:

CREATE FUNCTION CustomersByRegion 
(  
    @RegionID int  
)
RETURNS TABLE 
AS
RETURN 
  SELECT *
  FROM customers
  WHERE RegionID = @RegionID
GO

Sau đó, bạn có thể gọi hàm này để nhận kết quả như sau:

SELECT * FROM CustomersbyRegion(1)

Hoặc để làm một INTO SELECT:

SELECT * INTO CustList FROM CustomersbyRegion(1)

Nếu bạn vẫn cần một thủ tục lưu trữ, sau đó bọc các chức năng như vậy:

CREATE PROCEDURE uspCustomersByRegion 
(  
    @regionID int  
)
AS
BEGIN
     SELECT * FROM CustomersbyRegion(@regionID);
END
GO

Tôi nghĩ đây là phương pháp 'hack-less' nhất để đạt được kết quả mong muốn. Nó sử dụng các tính năng hiện có như chúng được dự định sẽ được sử dụng mà không có biến chứng thêm. Bằng cách lồng hàm nội tuyến do người dùng định nghĩa trong hàm được lưu trữ, bạn có quyền truy cập vào chức năng theo hai cách. Thêm! Bạn chỉ có một điểm duy trì cho mã SQL thực tế.

Việc sử dụng OPENROWSET đã được đề xuất, nhưng đây không phải là chức năng OPENROWSET được sử dụng cho (Từ Sách Trực tuyến):

Bao gồm tất cả thông tin kết nối   đó là cần thiết để truy cập dữ liệu từ xa   từ nguồn dữ liệu OLE DB. Điều này   phương pháp là một cách thay thế để truy cập   các bảng trong một máy chủ được liên kết và là một   một lần, phương pháp quảng cáo đặc biệt   và truy cập dữ liệu từ xa bằng cách sử dụng OLE   DB. Để tham khảo thường xuyên hơn để   Nguồn dữ liệu OLE DB, sử dụng liên kết   thay vào đó.

Sử dụng OPENROWSET sẽ nhận được công việc làm, nhưng nó sẽ phải chịu một số chi phí bổ sung để mở các kết nối cục bộ và dữ liệu marshalling. Nó cũng có thể không phải là một tùy chọn trong tất cả các trường hợp vì nó yêu cầu một quyền truy vấn đặc biệt mà đặt ra một nguy cơ bảo mật và do đó có thể không được mong muốn. Ngoài ra, cách tiếp cận OPENROWSET sẽ loại bỏ việc sử dụng các thủ tục được lưu trữ trả về nhiều tập kết quả. Việc gói nhiều hàm giá trị bảng do người dùng định nghĩa trong một quy trình được lưu trữ có thể đạt được điều này.


167
2017-08-04 17:11



+1 Một hàm bảng có giá trị là một giải pháp thích hợp. Chúng ta nên lưu ý về những hạn chế nhỏ: hàm table-valued là một đối tượng cơ sở dữ liệu phụ và có thể cần thiết để cấp các đặc quyền trên nó. - spencer7593
Yêu giải pháp. Một cú đánh nhỏ tôi nhấn, là bảng của tôi không thể có trật tự theo nơi như nó có thể có nó trong thủ tục được lưu trữ. Owh tốt, tôi sẽ sắp xếp nó ra - mrwaim
Thêm một lần nữa - "Không thể truy cập các bảng tạm thời từ bên trong một hàm" - mrwaim
Câu hỏi ban đầu là làm thế nào để chúng ta tạo ra một bảng tạm thời với kết quả của sp. Đây là một mô hình tốt, nhưng không giải quyết được câu hỏi này - greg
greg, dòng đầu tiên trong câu trả lời của tôi nói "Đây là câu trả lời cho một phiên bản được sửa đổi một chút của câu hỏi của bạn." Nhận xét của bạn là thừa. - Christian Loris


SELECT  *
INTO    #tmpTable
FROM    OPENQUERY(YOURSERVERNAME, 'EXEC test.dbo.prc_test 1')

101
2018-03-17 10:50



Nhận "Tên đối tượng Msg 208, Cấp 16, Tiểu bang 1, Dòng 1 không hợp lệ 'tmpBusLine' (có thể do nó không xác định trước). - Ferdeen
@ Ferds: xin lỗi, không hiểu yêu cầu của bạn lúc đầu. Cập nhật với một giải pháp khác. - Quassnoi
Giải pháp tuyệt vời. Một báo trước, bạn sẽ cần phải kích hoạt 'DATA ACCESS' trên máy chủ của bạn: EXEC sp_serveroption 'TheServerName', 'DATA ACCESS', TRUE - jcollum
Bạn cũng sẽ cần cho phép truy cập từ xa vào máy chủ. Điều này sẽ có phân nhánh bảo mật. - BraveNewMath
Điều này sẽ không hoạt động nếu thủ tục được lưu trữ đích sử dụng các bảng tạm thời - Sal


Giải pháp dễ nhất:

CREATE TABLE #temp (...);

INSERT INTO #temp
EXEC [sproc];

Nếu bạn không biết lược đồ thì bạn có thể làm như sau. Xin vui lòng lưu ý rằng có những rủi ro bảo mật nghiêm trọng trong phương pháp này.

SELECT * 
INTO #temp
FROM OPENROWSET('SQLNCLI', 
                'Server=localhost;Trusted_Connection=yes;', 
                'EXEC [db].[schema].[sproc]')

91
2018-03-13 19:38



nếu tôi không biết các cột resultset trả về sau đó ??? tôi có nghĩa là cột có thể khác nhau. vậy làm thế nào để chèn kết quả vào bảng tạm thời ??? - SHEKHAR SHETE
Bạn có thể sử dụng OPENQUERY nhưng nó không được khuyến khích vì nó đi kèm với các lỗi bảo mật. - Tigerjz32
"nếu tôi không biết các cột của resultset trả về sau đó" sau đó bạn không thể sử dụng nó trong logic của bạn. Bạn sẽ sử dụng dữ liệu như thế nào nếu bạn không biết nó là gì? - Adriaan Davel
@AdriaanDavel Tôi đồng ý với bạn rằng bạn nên luôn luôn biết dữ liệu của bạn (thực hành tốt nhất), tuy nhiên những gì ông có thể nói là có lần khi sproc trả về cột động và bạn không phải lúc nào cũng biết lược đồ sẽ trông như thế nào. Trong trường hợp đó, bạn có thể sử dụng OPENROWSET để chèn và tạo bảng khi đang di chuyển. Tuy nhiên, có những rủi ro bảo mật rõ ràng với việc này ... - Tigerjz32
SQLNCLI không hợp lệ đối với tôi? làm thế nào để bạn biết đó là nhà cung cấp OLEDB? - Danny Rancher


Khi thủ tục lưu trữ trả về rất nhiều cột và bạn không muốn "tạo" một bảng tạm thời theo cách thủ công để giữ kết quả, tôi đã tìm thấy cách dễ nhất là đi vào quy trình được lưu trữ và thêm một mệnh đề "vào" trên câu lệnh chọn cuối cùng và thêm 1 = 0 vào mệnh đề where.

Chạy thủ tục được lưu trữ một lần và quay trở lại và loại bỏ mã SQL bạn vừa thêm vào. Bây giờ, bạn sẽ có một bảng trống phù hợp với kết quả của thủ tục lưu sẵn. Bạn có thể "tạo bảng kịch bản" cho bảng tạm thời hoặc chỉ cần chèn trực tiếp vào bảng đó.


81
2018-03-17 14:06



1, gợi ý tuyệt vời. Bạn thậm chí có thể thêm một biến tùy chọn nhanh cho sproc được gọi là @TableCreate hoặc một cái gì đó tương tự mà khi không phải là null làm các bước trên. Không yêu cầu thay đổi của sproc sau đó một khi nó được thiết lập. - Ian Roke
@ dotjoe Bạn có làm một SELECT INTO một bảng tạm thời và làm một bảng kịch bản như tạo ra từ bảng tạm thời? Bảng tạm thời hiển thị trong tempdb nhưng tôi không thể nhấp chuột phải và tạo tập lệnh tạo. Bất kỳ trợ giúp được đánh giá cao. - DotnetDude
@DotNetDude bạn có thể select ... into new_table để ngầm tạo ra một bảng thực tế. - dotjoe
Sau đó lấy định nghĩa cột thô từ lược đồ bảng trống; thay thế '...' ở cuối bằng TABLE_NAME hợp lệ: declare @s varchar(max)='';select @s=@s+','+COLUMN_NAME+' '+DATA_TYPE+isnull('('+case CHARACTER_MAXIMUM_LENGTH when -1 then 'max' else cast(CHARACTER_MAXIMUM_LENGTH as varchar(10))end+')','')from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='...';select @s - user423430