Click here to Skip to main content
15,120,478 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
SQL
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);

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


What I have tried:

I have tried the above sql query but bot working.
Posted
Updated 5-Jan-17 8:47am
v4
Comments
David_Wimbley 5-Jan-17 11:12am
   
You should post your table schema for build_master and build_versions so people can try and replicate your problem.

1 solution

I'm not sure if I understand the situation correctly, but why do you have two output clauses? For example the following works fine
SQL
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
SQL
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
Of course you wouldn't need the last two select statements since they are only for checking the result from the batch.
   
v3
Comments
souvikcode 6-Jan-17 3:41am
   
see,buildVersionId is in the second table. so from your query how can I get that?
souvikcode 6-Jan-17 4:04am
   
see here, I need buildVersionId and that is in second table. So how can I return it?
souvikcode 7-Jan-17 0:10am
   
So, no solution for it?
Wendelius 7-Jan-17 5:59am
   
Have a look at the modified answer.
souvikcode 7-Jan-17 6:09am
   
two questions here.
1. you are inserting first into a temp table and then fetching,say for master record. I hope there will be no problem if multiple user simultaneously insert data,right?
2.select a.buildId,
'a',
1,
'a',
getdate(),
getdate(),
getdate(),
getdate(),
'a',
'a',
'a',
'a',
'a',
'a',
'a'
from @MasterOutput a;

in the place of 'a' there will be inserted data of master table. so the temp table @MasterOutput must have all the column same as the main database table,right?
Wendelius 7-Jan-17 6:31am
   
The table is not a temporary table, it's a table variable so the scope is the batch it's declared in. No one else will see it.

What comes to the 'a' values in the insert statement, you used variables such as @environment or @status. In order to test the situation, I just replaced them with constants but you would use the actual variables.

I believe that the @mastertable need only the columns that are returned from the first insert. At least it looks like the variables you use are not coming from the build_master table but from your program.
souvikcode 7-Jan-17 6:57am
   
Ok sir thanks. I'll try this query.
Wendelius 7-Jan-17 7:16am
   
Hopefully it works out :)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900