在sql中插入带有多个选择结果的语句 [英] Insert statement with multiple select results in sql
本文介绍了在sql中插入带有多个选择结果的语句的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
大家好,
i写了一个存储过程,我已经声明了游标。
使用光标,我得到了多个表格。
例如:
unit | rig
-----------
u1 | r1
u1 | r2
u1 | r3
uint | rig
----------
u2 | r1
u2 | r2
u2 | r3
i希望将这些作为unit1和unit2列名插入到一个表中。
存储过程
声明@UnitID int
DECLARE @tbl table(nvarchar(50),b nvarchar(50),c nvarchar(50),d nvarchar(50),e nvarchar(50),f nvarchar(50),g nvarchar(50),h nvarchar(50),i nvarchar(50),j nvarchar(50),k nvarchar( 50),l nvarchar(50),m nvarchar(50),n nvarchar(50),o nvarchar(50),p nvarchar(50))
为
声明UnitIDCursor游标SELECT来自tbl_Units的UnitID WHERE DepartmentID = 1
open UnitIDCursor
从UnitIDCursor获取下一个到@UnitID
while(@@ FETCH_STATUS = 0)
begin
--INSERT INTO @tbl(a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p)
select * from(选择
u.Unit_Name,r.rig_name作为RIG
来自tbl_JobPosted j
left join tbl_rigs r on r.rigid = j.rigid
inner join tbl_Units u on u.UnitID = j.UnitID
其中j.DepartmentID = 1
And(j.Posted_Date在'11 / 01/2014'和'11 / 30/2014'之间)
和u.UnitID = @UnitID
)temp
从UnitIDCursor获取下一个到@UnitID
结束
关闭UnitIDCursor
deallocate UnitIDCursor
任何人都可以帮助我。
谢谢
解决方案
在Temp表或表变量中选择查询的输出。
光标结束后从该表中选择数据。
需要以下查询输出才能存储
选择 * 来自(选择
u.Unit_Name,r.rig_name as RIG
来自 tbl_JobPosted j
left join tbl_rigs r on r.rigid = j.rigid
内部 join tbl_Units u on u.UnitID = j.UnitID
< span class =code-keyword>其中 j.DepartmentID = 1
和(j.Posted_Date ' 11/01 / 2014' 和 ' 11/30 / 2014')
和 u.UnitID = @ UnitID
)温度
Hi guys,
i wrote one stored procedure, where i have declared cursors.
using cursor, i got multiple table as result.
for eg:
unit | rig ----------- u1 | r1 u1 | r2 u1 | r3 uint | rig ---------- u2 | r1 u2 | r2 u2 | r3
i want to insert these into one table as unit1 and unit2 column names.
Stored Procedure
declare @UnitID int DECLARE @tbl table(a nvarchar(50), b nvarchar(50), c nvarchar(50), d nvarchar(50), e nvarchar(50), f nvarchar(50), g nvarchar(50),h nvarchar(50),i nvarchar(50),j nvarchar(50),k nvarchar(50),l nvarchar(50),m nvarchar(50),n nvarchar(50),o nvarchar(50),p nvarchar(50)) declare UnitIDCursor cursor for SELECT UnitID from tbl_Units WHERE DepartmentID = 1 open UnitIDCursor fetch next from UnitIDCursor into @UnitID while(@@FETCH_STATUS = 0) begin --INSERT INTO @tbl(a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p) select * from( select u.Unit_Name, r.rig_name as RIG from tbl_JobPosted j left join tbl_rigs r on r.rigid = j.rigid inner join tbl_Units u on u.UnitID = j.UnitID where j.DepartmentID = 1 And (j.Posted_Date between '11/01/2014' and '11/30/2014') and u.UnitID = @UnitID )temp fetch next from UnitIDCursor into @UnitID end close UnitIDCursor deallocate UnitIDCursor
can any one please help me.
Thanks
解决方案
hi,
take the output of select query in Temp table or table variable.
after end of cursor select data from that table.
Need below query output to be store
select * from( select u.Unit_Name, r.rig_name as RIG from tbl_JobPosted j left join tbl_rigs r on r.rigid = j.rigid inner join tbl_Units u on u.UnitID = j.UnitID where j.DepartmentID = 1 And (j.Posted_Date between '11/01/2014' and '11/30/2014') and u.UnitID = @UnitID )temp
这篇关于在sql中插入带有多个选择结果的语句的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文