sql شرح - إدراج نتائج إجراء مخزن في جدول مؤقت




stored procedure (21)

كيف أفعل SELECT * INTO [temp table] FROM [stored procedure] ؟ ليس FROM [Table] وبدون تحديد [temp table] ؟

Select جميع البيانات من BusinessLine إلى tmpBusLine يعمل بشكل جيد.

select *
into tmpBusLine
from BusinessLine

أنا أحاول نفس الشيء ، ولكن استخدام إجراء stored procedure بإرجاع البيانات ، ليس تماماً كما هو.

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

رسالة الإخراج:

Msg 156، Level 15، State 1، Line 2 Incorrect syntax near the word 'exec'.

لقد قرأت عدة أمثلة على إنشاء جدول مؤقت له نفس بنية الإجراء المخزن المخزن ، والذي يعمل بشكل جيد ، ولكن سيكون من الجيد عدم توفير أي أعمدة.


Answers

إذا كنت تريد القيام بذلك دون تعريف الجدول المؤقت أولاً ، يمكنك محاولة إنشاء دالة معرفة من قبل المستخدم بدلاً من إجراء مخزن وجعل الدالة المعرفة من قبل المستخدم بإرجاع جدول. بالتناوب ، إذا كنت ترغب في استخدام الإجراء المخزن ، فجرّب شيئًا كهذا:

CREATE TABLE #tmpBus
(
   COL1 INT,
   COL2 INT
)

INSERT INTO #tmpBus
Exec SpGetRecords 'Params'

Another method is to create a type and use PIPELINED to then pass back your object. This is limited to knowing the columns however. But it has the advantage of being able to do:

SELECT * 
FROM TABLE(CAST(f$my_functions('8028767') AS my_tab_type))

هذا proc المخزنة يقوم بهذه المهمة:

CREATE PROCEDURE [dbo].[ExecIntoTable]
(
    @tableName          NVARCHAR(256),
    @storedProcWithParameters   NVARCHAR(MAX)
)
AS
BEGIN
    DECLARE @driver         VARCHAR(10)
    DECLARE @connectionString   NVARCHAR(600)
    DECLARE @sql            NVARCHAR(MAX)
    DECLARE @rowsetSql      NVARCHAR(MAX)

    SET @driver = '''SQLNCLI'''

    SET @connectionString = 
        '''server=' + 
            CAST(SERVERPROPERTY('ServerName') AS NVARCHAR(256)) + 
            COALESCE('\' + CAST(SERVERPROPERTY('InstanceName') AS NVARCHAR(256)), '') + 
        ';trusted_connection=yes'''

    SET @rowsetSql = '''EXEC ' + REPLACE(@storedProcWithParameters, '''', '''''') + ''''

    SET @sql = '
SELECT
    *
INTO 
    ' + @tableName + ' 
FROM
    OPENROWSET(' + @driver + ',' + @connectionString + ',' + @rowsetSql + ')'

    EXEC (@sql)
END
GO

إنها إعادة صياغة بسيطة لهذا: إدراج نتائج الإجراءات المخزنة في الجدول بحيث تعمل في الواقع.

إذا كنت تريد أن تعمل مع جدول مؤقت ، فستحتاج إلى استخدام جدول ##GLOBAL وإسقاطه بعد ذلك.


إذا كنت محظوظًا بما يكفي للحصول على SQL 2012 أو أعلى ، فيمكنك استخدام dm_exec_describe_first_result_set_for_object

لقد قمت للتو بتحرير مزود قدمتها gotqn. شكرا gotqn.

يؤدي ذلك إلى إنشاء جدول مؤقت عالمي يحمل نفس اسم اسم الإجراء. يمكن استخدام الجدول المؤقت لاحقًا كما هو مطلوب. لا تنس أن تسقطها قبل إعادة التنفيذ.

    declare @procname nvarchar(255) = 'myProcedure',
            @sql nvarchar(max) 

    set @sql = 'create table ##' + @procname + ' ('
    begin
            select      @sql = @sql + '[' + r.name + '] ' +  r.system_type_name + ','
            from        sys.procedures AS p
            cross apply sys.dm_exec_describe_first_result_set_for_object(p.object_id, 0) AS r
            where       p.name = @procname

            set @sql = substring(@sql,1,len(@sql)-1) + ')'
            execute (@sql)
            execute('insert ##' + @procname + ' exec ' + @procname)
    end

إذا كان الاستعلام لا يحتوي على معلمة ، فاستخدم OpenQuery else استخدم OpenRowset .

الشيء الأساسي سيكون إنشاء مخطط حسب الإجراء المخزن وإدراج في هذا الجدول. على سبيل المثال:

DECLARE @abc TABLE(
                  RequisitionTypeSourceTypeID INT
                , RequisitionTypeID INT
                , RequisitionSourcingTypeID INT
                , AutoDistOverride INT
                , AllowManagerToWithdrawDistributedReq INT
                , ResumeRequired INT
                , WarnSupplierOnDNRReqSubmission  INT
                , MSPApprovalReqd INT
                , EnableMSPSupplierCounterOffer INT
                , RequireVendorToAcceptOffer INT
                , UseCertification INT
                , UseCompetency INT
                , RequireRequisitionTemplate INT
                , CreatedByID INT
                , CreatedDate DATE
                , ModifiedByID INT
                , ModifiedDate DATE
                , UseCandidateScheduledHours INT
                , WeekEndingDayOfWeekID INT
                , AllowAutoEnroll INT
                )
INSERT INTO @abc
EXEC [dbo].[usp_MySp] 726,3
SELECT * FROM @abc

إذا كان OPENROWSET يسبب لك مشاكل ، فهناك طريقة أخرى من عام 2012 فصاعدا. الاستفادة من sys.dm_exec_describe_first_result_set_for_object ، كما ذكر هنا: استرداد أسماء الأعمدة وأنواع إجراء مخزن؟

أولاً ، قم بإنشاء هذا الإجراء المخزن لإنشاء SQL للوقت المؤقت

CREATE PROCEDURE dbo.usp_GetStoredProcTableDefinition(
    @ProcedureName  nvarchar(128),
    @TableName      nvarchar(128),
    @SQL            nvarchar(max) OUTPUT
)
AS
SET @SQL = 'CREATE TABLE ' + @tableName + ' ('

SELECT @SQL = @SQL + '['+name +'] '+ system_type_name +''  + ','
        FROM sys.dm_exec_describe_first_result_set_for_object
        (
          OBJECT_ID(@ProcedureName), 
          NULL
        );

--Remove trailing comma
SET @SQL = SUBSTRING(@SQL,0,LEN(@SQL))    
SET @SQL =  @SQL +')'

لاستخدام الإجراء ، قم باستدعائه بالطريقة التالية:

DECLARE     @SQL    NVARCHAR(MAX)

exec dbo.usp_GetStoredProcTableDefinition
    @ProcedureName='dbo.usp_YourProcedure',
    @TableName='##YourGlobalTempTable',@SQL = @SQL OUTPUT

INSERT INTO ##YourGlobalTempTable
EXEC    [dbo].usp_YourProcedure

select * from ##YourGlobalTempTable

لاحظ أنني أستخدم جدول مؤقت عام. هذا لأن استخدام EXEC لتشغيل SQL الديناميكي يقوم بإنشاء جلسة خاصة به ، لذا فإن الجدول المؤقت العادي سيكون خارج النطاق لأي كود لاحق. إذا كان الجدول المؤقت العام مشكلة ، يمكنك استخدام جدول مؤقت عادي ، ولكن أي SQL لاحقة ستحتاج إلى أن تكون ديناميكية ، والتي يتم تنفيذها أيضًا بواسطة عبارة EXEC.


هل يقوم الإجراء المخزن باسترداد البيانات فقط أو تعديلها أيضًا؟ إذا تم استخدامه فقط للاسترجاع ، يمكنك تحويل الإجراء المخزن إلى دالة واستخدام تعبيرات جدول شائعة (CTE) دون الحاجة إلى إعلانه ، كما يلي:

with temp as (
    select * from dbo.fnFunctionName(10, 20)
)
select col1, col2 from temp

ومع ذلك ، ينبغي استخدام أي شيء يحتاج إلى استرجاعه من CTE في بيان واحد فقط. لا يمكنك القيام with temp as ... ومحاولة استخدامه بعد سطرين من SQL. يمكن أن يكون لديك CTEs متعددة في عبارة واحدة لاستعلامات أكثر تعقيداً.

فمثلا،

with temp1020 as (
    select id from dbo.fnFunctionName(10, 20)
),
temp2030 as (
    select id from dbo.fnFunctionName(20, 30)
)
select * from temp1020 
where id not in (select id from temp2030)

This can be done in SQL Server 2014+ provided SP only returns one table. If anyone finds a way of doing this for multiple tables I'd love to know about it.

DECLARE @storeProcname NVARCHAR(MAX) = ''

SET @storeProcname = 'myStoredProc'

DECLARE @strSQL AS VARCHAR(MAX) = 'CREATE TABLE myTableName '

SELECT @strSQL = @strSQL+STUFF((
SELECT ',' +name+' ' + system_type_name 
FROM sys.dm_exec_describe_first_result_set_for_object (OBJECT_ID(@storeProcname),0)
FOR XML PATH('')
),1,1,'(') + ')'

EXEC (@strSQL)

INSERT INTO myTableName
EXEC ('myStoredProc @param1=1, @param2=2')

SELECT * FROM myTableName

DROP TABLE myTableName

This pulls the definition of the returned table from system tables, and uses that to build the temp table for you. You can then populate it from the SP as stated before.

There are also variants of this that work with Dynamic SQL too.


لقد وجدت Passing Arrays / DataTables في إجراءات مخزنة قد تعطيك فكرة أخرى عن كيفية حل مشكلتك.

يقترح الارتباط استخدام معلمة نوع صورة لتمريرها إلى الإجراء المخزن. ثم في الإجراء المخزن ، يتم تحويل الصورة إلى متغير جدول يحتوي على البيانات الأصلية.

ربما هناك طريقة يمكن استخدامها مع جدول مؤقت.


عندما يقوم الإجراء المخزن بإرجاع الكثير من الأعمدة ولا تريد يدوياً "إنشاء" جدول مؤقت للاحتفاظ بالنتيجة ، لقد وجدت أن أسهل طريقة هي الانتقال إلى الإجراء المخزن وإضافة جملة "إلى" على آخر اختيار بيان وإضافة 1 = 0 إلى حيث جملة.

قم بتشغيل الإجراء المخزن مرة واحدة والعودة وإزالة رمز SQL الذي أضفته للتو. الآن ، سيكون لديك جدول فارغ يطابق نتيجة الإجراء المخزن. يمكنك إما "الجدول النصي كما خلق" لجدول مؤقت أو ببساطة إدراج مباشرة في هذا الجدول.


declare @temp table
(
    name varchar(255),
    field varchar(255),
    filename varchar(255),
    filegroup varchar(255),
    size varchar(255),
    maxsize varchar(255),
    growth varchar(255),
    usage varchar(255)
);
INSERT @temp  Exec sp_helpfile;
select * from @temp;

If you know the parameters that are being passed and if you don't have access to make sp_configure, then edit the stored procedure with these parameters and the same can be stored in a ##global table.


الحل الأسهل:

CREATE TABLE #temp (...);

INSERT INTO #temp
EXEC [sproc];

إذا كنت لا تعرف المخطط ، فيمكنك القيام بما يلي. يرجى ملاحظة أن هناك مخاطر أمنية شديدة في هذه الطريقة.

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

يمكنك استخدام OPENROWSET لهذا. الق نظرة. لقد قمت أيضًا بتضمين رمز sp_configure لتمكين Ad Hoc Distributed Queries ، في حالة عدم تمكينه بالفعل.

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

لإدراج مجموعة السجلات الأولى من الإجراء المخزن في جدول مؤقت تحتاج إلى معرفة ما يلي:

  1. يمكن إدراج مجموعة الصف الأول فقط من الإجراء المخزن في جدول مؤقت
  2. يجب أن لا تنفيذ الإجراء المخزن عبارة T-SQL الحيوية ( sp_executesql )
  3. تحتاج إلى تحديد بنية الجدول المؤقت أولاً

قد يبدو أعلاه كقيد ، ولكن IMHO يكون منطقيًا تمامًا - إذا كنت تستخدم sp_executesql يمكنك إرجاع عمودين مرة واحدة وعشر مرة ، وإذا كان لديك مجموعات نتائج متعددة ، فلا يمكنك إدراجها في عدة جداول أيضًا - يمكنك إدراج الحد الأقصى في جهازي في عبارة T-SQL (باستخدام جملة OUTPUT و بدون مشغلات).

لذلك ، تكون المشكلة بشكل أساسي كيفية تعريف بنية الجدول المؤقتة قبل تنفيذ العبارة EXEC ... INTO ...

يعمل الأول مع OBJECT_ID بينما يعمل الثاني والثالث مع استعلامات Ad-hoc أيضاً. أفضل استخدام DMV بدلاً من sp كما يمكنك استخدام CROSS APPLY ثم إنشاء تعريفات الجدول المؤقت لإجراءات متعددة في نفس الوقت.

SELECT p.name, r.* 
FROM sys.procedures AS p
CROSS APPLY sys.dm_exec_describe_first_result_set_for_object(p.object_id, 0) AS r;

أيضًا ، انتبه إلى حقل system_type_name لأنه يمكن أن يكون مفيدًا للغاية. يخزن العمود تعريف كامل. فمثلا:

smalldatetime
nvarchar(max)
uniqueidentifier
nvarchar(1000)
real
smalldatetime
decimal(18,2)

ويمكنك استخدامه مباشرة في معظم الحالات لإنشاء تعريف الجدول.

لذا ، أعتقد أنه في معظم الحالات (إذا كان الإجراء المخزن يتطابق مع معايير معينة) ، يمكنك بسهولة إنشاء عبارات ديناميكية لحل مثل هذه المشكلات (إنشاء الجدول المؤقت ، وإدراج نتيجة الإجراء المخزن فيه ، والقيام بما تحتاجه مع البيانات) .

لاحظ أن الكائنات أعلاه تفشل في تعريف بيانات مجموعة النتائج الأولى في بعض cases مثل عند تنفيذ عبارات T-SQL الديناميكية أو استخدام الجداول المؤقتة في الإجراء المخزن.


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

I would do the following

  1. Create (convert SP to) a UDF (Table value UDF).

  2. select * into #tmpBusLine from dbo.UDF_getBusinessLineHistory '16 Mar 2009'


هذه إجابة على نسخة معدلة قليلاً من سؤالك. إذا كان بإمكانك التخلي عن استخدام إجراء مخزن لدالة معرفة من قبل المستخدم ، فيمكنك استخدام دالة معرّفة من قبل المستخدم مضمنة الجدول. هذا بشكل أساسي إجراء مخزن (ستتخذ معلمات) إرجاع جدول كمجموعة نتائج؛ وبالتالي سوف تضع بشكل جيد مع بيان INTO.

هنا مقالة سريعة جيدة عليها ووظائف أخرى محددة من قبل المستخدم. إذا كان لا يزال لديك حاجة إلى محرك أقراص لإجراء مخزن ، يمكنك لف الدالة المضمنة من قبل المستخدم المضمنة في الجدول باستخدام إجراء مخزن. الإجراء المخزن فقط يمرر المعلمات عندما يقوم باستدعاء select * من دالة معرفة من قبل المستخدم مضمنة جدول الجدول.

على سبيل المثال ، سيكون لديك وظيفة معرّفة من قِبل المستخدم ومحددة القيمة للجدول للحصول على قائمة عملاء لمنطقة معينة:

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

يمكنك بعد ذلك استدعاء هذه الوظيفة للحصول على نتائجك على النحو التالي:

SELECT * FROM CustomersbyRegion(1)

أو للقيام SELECT INTO:

SELECT * INTO CustList FROM CustomersbyRegion(1)

إذا كنت لا تزال بحاجة إلى إجراء مخزن ، فقم بلف الوظيفة على هذا النحو:

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

أعتقد أن هذه هي طريقة "الاختراق أقل" للحصول على النتائج المرجوة. ويستخدم الميزات الموجودة كما كان يقصد استخدامها دون مضاعفات إضافية. من خلال تداخل وظيفة معرّفة من قبل المستخدم مضمّنة في الجدول في الإجراء المخزن ، يمكنك الوصول إلى الوظيفة بطريقتين. زائد! لديك نقطة صيانة واحدة فقط لرمز SQL الفعلي.

تم اقتراح استخدام OPENROWSET ، ولكن هذا ليس ما تم إعداده لاستخدام الدالة OPENROWSET لـ (من كتب عبر الإنترنت):

يتضمن كافة معلومات الاتصال المطلوبة للوصول إلى البيانات البعيدة من مصدر بيانات OLE DB. تعتبر هذه الطريقة بديلاً للوصول إلى الجداول في ملقم مرتبط وهي عبارة عن طريقة مؤقتة مخصصة للاتصال والوصول إلى البيانات البعيدة باستخدام OLE DB. لمزيد من المراجع المتكررة إلى مصادر بيانات OLE DB ، استخدم الخوادم المرتبطة بدلاً من ذلك.

باستخدام OPENROWSET سيتم إنجاز المهمة ، ولكنه سيحمل بعض الحمل الإضافي لفتح الاتصالات المحلية وتنظيم البيانات. كما أنه قد لا يكون خيارًا في جميع الحالات حيث إنه يتطلب تصريح استعلام مخصصًا والذي يشكل خطرًا أمنيًا وبالتالي قد لا يكون مرغوبًا. أيضاً ، سيحول النهج OPENROWSET دون استخدام الإجراءات المخزنة إرجاع أكثر من مجموعة نتائج واحدة. يمكن تحقيق التفاف العديد من وظائف معرّفة من قِبل المستخدم - قيمة الجدول المضمنة في إجراء مخزن واحد هذا.


إذا كان جدول نتائج proc المخزنة معقدًا للغاية لكتابة عبارة "إنشاء جدول" يدويًا ، ولا يمكنك استخدام OPENQUERY أو OPENROWSET ، يمكنك استخدام sp_help لإنشاء قائمة الأعمدة وأنواع البيانات نيابة عنك. وبمجرد الانتهاء من قائمة الأعمدة ، فإنها مجرد مسألة تنسيقها لتناسب احتياجاتك.

الخطوة 1: إضافة "إلى #temp" إلى استعلام الإخراج (على سبيل المثال "حدد [...] إلى #temp من [...]").

أسهل طريقة لتحرير استعلام الإخراج في proc مباشرة. إذا لم تتمكن من تغيير البرنامج المخزن ، يمكنك نسخ المحتويات إلى نافذة استعلام جديدة وتعديل الاستعلام هناك.

الخطوة 2: قم بتشغيل sp_help على الجدول المؤقت. (على سبيل المثال "exec tempdb..sp_help #temp")

بعد إنشاء الجدول المؤقت ، قم بتشغيل sp_help على الجدول المؤقت للحصول على قائمة بالأعمدة وأنواع البيانات بما في ذلك حجم حقول varchar.

الخطوة 3: قم بنسخ أعمدة البيانات وأنواعها في بيان جدول إنشاء

لدي ورقة Excel التي أستخدمها لتنسيق إخراج sp_help إلى عبارة "إنشاء جدول". لا تحتاج إلى أي شيء يتوهم ، فقط قم بنسخه ولصقه في محرر SQL. استخدم أسماء الأعمدة والأحجام والأنواع لإنشاء "إنشاء جدول #x [...]" أو "statementx table [...]" الذي يمكنك استخدامه لإدراج نتائج الإجراء المخزن.

الخطوة 4: أدخل في الجدول المنشأ حديثًا

سيكون لديك الآن طلب بحث مثل الحلول الأخرى الموضحة في سلسلة المحادثات هذه.

DECLARE @t TABLE 
(
   --these columns were copied from sp_help
   COL1 INT,
   COL2 INT   
)

INSERT INTO @t 
Exec spMyProc 

يمكن استخدام هذه التقنية أيضًا لتحويل جدول مؤقت ( #temp ) إلى متغير جدول ( @temp ). على الرغم من أن هذا قد يكون أكثر من مجرد كتابة عبارة create table بنفسك ، إلا أنه يمنع حدوث خطأ يدوي مثل الأخطاء المطبعية ونوع بيانات عدم التطابق في العمليات الكبيرة. قد يستغرق تصحيح أخطاء مطبعي وقتًا أطول من كتابة طلب البحث في المقام الأول.


I met the same problem and here is what I did for this from Paul's suggestion . The main part is here is to use NEWID() to avoid multiple users run the store procedures/scripts at the same time, the pain for global temporary table.

DECLARE @sql varchar(max) = '', 
@tmp_global_table varchar(255) = '##global_tmp_' + CONVERT(varchar(36), NEWID())
SET @sql = @sql + 'select * into [' + @tmp_global_table + '] from YOURTABLE'
EXEC(@sql)

EXEC('SELECT * FROM [' + @tmp_global_table + ']')

INSERT التي تستخدم بناء الجملة VALUES يمكنها إدراج صفوف متعددة. للقيام بذلك ، قم بتضمين قوائم متعددة لقيم العمود ، كل منها ضمن أقواس و مفصولة بفواصل.

مثال:

INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);




sql sql-server sql-server-2005 tsql stored-procedures