如何将输出中的输出多数据返回到SQL查询中 [英] How to return output multi data in output into SQL query

问题描述

insert into build_master(lastUpdatedOn) output inserted.buildId,@versionName,@devrecordId,@developedBy,@reportDate,@scheduledDate,
@implementationdate,@closeDate,@environment,@status,@knownIssue,@comments,
@functionAdded,@functionUpdated,@defectsFixed into  build_versions(buildId,versionName,devrecordId,developedBy,reportDate,scheduledDate,
implementationdate,closeDate,environment,status,knownIssue,comments,functionAdded,
functionUpdated,defectsFixed) output inserted.buildId,inserted.buildVersionId
               values(@lastupdatedon);

带下划线的部分将从第二个表格返回。第二个表有自动生成id列名为

The underlined portion will be returned from the second table. The second table has auto generate id column named "

buildVersionId

。但是我收到了无效列的错误。

这意味着输出子句不返回数据从第二张表开始。

. But I'm getting error of invalid column.
That means output clause not returning data from the second table.

USE [abc]
GO
/****** Object:  Table [dbo].[build_master]    Script Date: 05-Jan-17 10:26:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[build_master](
	[buildId] [bigint] IDENTITY(1,1) NOT NULL,
	[lastUpdatedOn] [datetime] NOT NULL,
 CONSTRAINT [PK_build_master] PRIMARY KEY CLUSTERED 
(
	[buildId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[build_versions]    Script Date: 05-Jan-17 10:26:09 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[build_versions](
	[buildVersionId] [bigint] IDENTITY(1,1) NOT NULL,
	[buildId] [bigint] NOT NULL,
	[versionName] [nvarchar](50) NOT NULL,
	[devrecordId] [int] NOT NULL,
	[developedBy] [nvarchar](max) NOT NULL,
	[reportDate] [datetime] NOT NULL,
	[scheduledDate] [datetime] NULL,
	[implementationdate] [datetime] NULL,
	[closeDate] [datetime] NULL,
	[environment] [nvarchar](50) NOT NULL,
	[status] [nvarchar](50) NOT NULL,
	[knownIssue] [nvarchar](max) NULL,
	[comments] [nvarchar](max) NULL,
	[functionAdded] [nvarchar](max) NOT NULL,
	[functionUpdated] [nvarchar](max) NULL,
	[defectsFixed] [nvarchar](max) NULL,
 CONSTRAINT [PK_build_versions] PRIMARY KEY CLUSTERED 
(
	[buildVersionId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [IX_build_versions] UNIQUE NONCLUSTERED 
(
	[buildId] ASC,
	[versionName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
ALTER TABLE [dbo].[build_versions] ADD  CONSTRAINT [DF_build_versions_environment]  DEFAULT ('Select One') FOR [environment]
GO
ALTER TABLE [dbo].[build_versions] ADD  CONSTRAINT [DF_build_versions_status]  DEFAULT ('Open') FOR [status]
GO

我尝试了什么：



我已经尝试过上面的sql查询但是机器人工作。

What I have tried:

I have tried the above sql query but bot working.

推荐答案

我不确定我是否正确了解情况，但为什么你有两个输出条款？例如，以下工作正常

I'm not sure if I understand the situation correctly, but why do you have two output clauses? For example the following works fine

insert into build_master(lastUpdatedOn) 
output inserted.buildId,
		'a',
		1,
		'a',
		getdate(),
		getdate(), 
		getdate(),
		getdate(),
		'a',
		'a',
		'a',
		'a',
		'a',
		'a',
		'a' 
into  build_versions(
		buildId,
		versionName,
		devrecordId,
		developedBy,
		reportDate,
		scheduledDate,
		implementationdate,
		closeDate,
		environment,
		status,
		knownIssue,
		comments,
		functionAdded,
		functionUpdated,
		defectsFixed) 
--output inserted.buildId,inserted.buildVersionId
values(getdate());

[已添加]

一次运行可能很难从两个表中获取值。你能在同一批次中使用两次运行吗？请考虑以下内容

[ADDED]
It may be hard to get values from both tables in a single run. Could you use two runs in the same batch. Consider the following

declare @MasterOutput TABLE  (  
    buildId bigint
);  

declare @VersionsOutput TABLE  (  
    buildId bigint,
    buildversionid bigint
);  

insert into build_master(lastUpdatedOn) 
output inserted.buildId
into @MasterOutput
values (getdate());

insert into build_versions (
		buildId,
		versionName,
		devrecordId,
		developedBy,
		reportDate,
		scheduledDate,
		implementationdate,
		closeDate,
		environment,
		status,
		knownIssue,
		comments,
		functionAdded,
		functionUpdated,
		defectsFixed) 

output inserted.buildId,inserted.buildVersionId
into @VersionsOutput
select a.buildId,
		'a',
		1,
		'a',
		getdate(),
		getdate(), 
		getdate(),
		getdate(),
		'a',
		'a',
		'a',
		'a',
		'a',
		'a',
		'a' 
from @MasterOutput a;

select * from @MasterOutput;

select * from @VersionsOutput;

输出为

The output is

buildId
-------
7

buildId   buildversionid
-------   --------------
7         4

当然你不会'需要最后两个select语句，因为它们仅用于检查批处理的结果。

Of course you wouldn't need the last two select statements since they are only for checking the result from the batch.

这篇关于如何将输出中的输出多数据返回到SQL查询中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！