Inserting Data with nested select

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Inserting Data with nested select

Author

Topic

mayoorsubbu
Yak Posting Veteran

95 Posts

Posted - 2011-02-09 : 08:53:40

Dear Gurus,

I have a table dbo.RequestMaster. I am inserting data and one of the value is an id no. I want to fetch the name value corresponding to the id no and insert it into the Requestmaster table. I know it can be achieved through a insert statement followed by an update. How can I do it in one single insert statement. Want I want to acheive is as follows :-


INSERT INTO [dbo].[RequestMaster] (RequestId, Unitid, RequestNo, RequestDt, RecdDt, RegnDt, Remarks, unittype, dateupdated, updatedby, unit) 
VALUES (@cNextId, @cUnit, upper(@cRequestno), 
cast(@dRequestDt as datetime),cast(@dRecdDt as datetime), GETDATE(), 
upper(@cRemarks), @unittype, getdate(), @uid, (select unit from dbo.unitmaster UNION select institution_name as unit 
from dbo.tbglInstmaster where dbo.unitmaster.unitid=@cUnit or
dbo.tbglInstmaster.Id=@cUnit);

Hope my intent is clear.
Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-02-09 : 09:07:53

Try this

INSERT INTO [dbo].[RequestMaster] (RequestId, Unitid, RequestNo, RequestDt, RecdDt, RegnDt, Remarks, unittype, dateupdated, updatedby, unit)
SELECT @cNextId, @cUnit, upper(@cRequestno), cast(@dRequestDt as datetime),cast(@dRecdDt as datetime), GETDATE(), upper(@cRemarks), @unittype, getdate(), @uid, unit from dbo.unitmaster
UNION ALL
select @cNextId, @cUnit, upper(@cRequestno), cast(@dRequestDt as datetime),cast(@dRecdDt as datetime), GETDATE(), upper(@cRemarks), @unittype, getdate(), @uid, institution_name as unit from dbo.tbglInstmaster where dbo.unitmaster.unitid=@cUnit or dbo.tbglInstmaster.Id=@cUnit;

Madhivanan

Failing to plan is Planning to fail