使用EXEC动态等效于INSERT INTO [英] Dynamic equivalent of INSERT INTO using EXEC

问题描述

我正在尝试创建以下等效项：

I am trying to create the equivalent of the following:

DROP TABLE IF EXISTS [jerry].[dbo].[purchases]
SELECT * INTO [jerry].[dbo].[purchases] FROM OPENQUERY(OLAP, '

    sql code
');

使用 EXEC （在此处查看此问题）

话虽如此，但根据 SELECT * INTO into-table-name-exec-usp-name>多个来源（和这个）

With that being said, I am unable to use SELECT * INTO according to multiple sources (and this one)

我找到了一些其他资源，可以在其中使用<$ c $创建新表c> EXEC ，但是，我不知道结果表的确切结构（列数，列类型，列名等），因此它必须是动态的。

I have found some other resources where I can create a new table using EXEC, however, I do not know the exact structure of the resulting table (number of columns, column types, column names, etc) so it needs to be dynamic.

以下代码使我得到一个我想要的精确结果表，但我无法弄清楚如何使用以下查询中的结果数据创建购买 $ c表b
$ b

The following code results in giving me the exact resulting table that I want, but I have not been able to figure out how to create the purchases table with the resulting data from the below query:

-- EXEC master.dbo.sp_serveroption @server=N'OLAP', @optname=N'rpc out', @optvalue=N'true'
DECLARE @sqlcode VARCHAR(MAX)
SET @sqlcode = 'code'
EXEC (@sqlcode) AT OLAP

我尝试使用以下命令： SELECT * INTO [jerry]。[dbo]。[purchases] FROM OPENROWSET（'EXEC（@sqlcode）AT OLAP'），但出现错误 '）'附近的语法不正确。

I have tried using the following: SELECT * INTO [jerry].[dbo].[purchases] FROM OPENROWSET('EXEC (@sqlcode) AT OLAP') but get an error of Incorrect syntax near ')'.

我也尝试过（只是看一下）：

I have also tried (just to see):

CREATE TABLE [jerry].[dbo].[purchases] ([Transaction_Date] DATE, [Requirement_Date] DATE, [Element] NVARCHAR(256), [Trx_Quantity] NVARCHAR(256), [Part_Number] NVARCHAR(256), [NHA_Part_Number] NVARCHAR(256), [Group] NVARCHAR(256), [Details] NVARCHAR(256));
INSERT INTO [jerry].[dbo].[purchases]
EXEC (@sqlcode) AT OLAP

并获得：

OLE DB provider "OraOLEDB.Oracle" for linked server "OLAP" returned message "Unable to enlist in the transaction.".
Msg 7391, Level 16, State 2, Line 208
The operation could not be performed because OLE DB provider "OraOLEDB.Oracle" for linked server "OLAP" was unable to begin a distributed transaction.

错误。

很抱歉，这是一个简单的逻辑问题-我感觉自己正在用尽我的研究能力来尝试寻找解决方案，因为我对SQL Server还是很陌生。我也正在SSMS 2017中工作，如果有帮助的话。

Apologies if this is an easy logic question -- I feel like I am exhausting my researching ability trying to find a solution as I am very new to SQL Server. I am working in SSMS 2017, as well, if that helps.

推荐答案

答案可以在此帖子中找到：
使用EXEC的结果创建新表

The answer can be found at this post: Create new table with results from EXEC

DECLARE @sqlcode VARCHAR(MAX)
SET @sqlcode = 'sqlcode'

truncate table [jerry].[dbo].[purchases]
insert into [jerry].[dbo].[purchases]
exec ( @sqlcode ) at OLAP

这篇关于使用EXEC动态等效于INSERT INTO的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！