如何在不使用游标的情况下为每个公司插入记 [英] How to insert records per company without using cursor
本文介绍了如何在不使用游标的情况下为每个公司插入记的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
Hii,
有时我需要在每个公司的表格中插入一个字段,所以我使用游标为该公司插入行数据..
DECLARE < span class =code-sdkkeyword> @ CompanyId int
DECLARE AbsenceType_cursor CURSOR FOR
选择 distinct companyid 来自 ABC
订单 按 companyid
OPEN AbsenceType_cursor
FETCH NEXT FROM AbsenceType_cursor
INTO @ CompanyId
WHILE @@ FETCH_STATUS = 0
BEGIN
将 插入 XYZ(' Annual ', @ companyId )
FETCH NEXT FROM AbsenceType_cursor
INTO @ CompanyId
END
CLOSE AbsenceType_cursor
DEALLOCATE AbsenceType_cursor
但现在,当数据更多要插入时,我也有很多公司..光标花了这么多时间插入..
请苏为我的这个场景添加了另一种选择...
解决方案
您可以创建一个Temp表来保存公司ID。然后可以使用while循环来循环到那里的每个ID。有关详细信息,请参阅此链接
http:// www .sqlbook.com / SQL / Avoiding-using-SQL-Cursors-20.aspx
声明 @ company table (
companyid int
)
insert into @ company (companyid)
( select distinct companyid 来自 ABC)
声明 @ cmpny int
while 存在( select 顶部 1 * 来自 @公司)
开始
set @ cmpny =(选择 top 1 companyid 来自 @ company )
insert into XYZ
values (< span class =code-string>' 年度', @ cmpny )
delete 来自 @ company 其中 companyid = @ cmpny
end
使用循环的替代方法 - 插入Select语句。
测试设置SQL:
声明 @ ABC table (
id int < span class =code-keyword> identity ( 1 , 1 ),
companyid int
)
insert 进入 @ ABC 值( 1 跨度>);
插入 进入 @ ABC 值( 3 );
插入 进入 @ ABC 值( 5 );
插入 进入 @ ABC 值( 5 );
插入 进入 @ ABC 值( 10 );
声明 @ XYZ 表(
period varchar ( 50 ),
companyid int
)
要插入@XYZ的SQL:
插入 进入 @ XYZ
(期间,companyid)
选择 distinct
' 年度',companyid
来自 @ ABC ;
什么在@ABC和@XYZ:
选择 * 来自 @ ABC ;
选择 * 来自 @ XYZ
Hii ,
Sometimes i required to insert a field into table which will be for per company , so i use cursor to insert row by row data for that company ..
DECLARE @CompanyId int
DECLARE AbsenceType_cursor CURSOR FOR
select distinct companyid from ABC
order by companyid
OPEN AbsenceType_cursor
FETCH NEXT FROM AbsenceType_cursor
INTO @CompanyId
WHILE @@FETCH_STATUS = 0
BEGIN
Insert into XYZ('Annual',@companyId)
FETCH NEXT FROM AbsenceType_cursor
INTO @CompanyId
END
CLOSE AbsenceType_cursor
DEALLOCATE AbsenceType_cursor
But now , when data is more to insert , and i also have many companies.. cursor is taking so much time to insert ..
Please suggest me alternative for my this scenario ...
解决方案
You can create a Temp table for keeping the company ID. And then can use a while loop for looping to every ID in there. Please refer this link for more details
http://www.sqlbook.com/SQL/Avoiding-using-SQL-Cursors-20.aspx
declare @company table( companyid int ) insert into @company(companyid) (select distinct companyid from ABC) declare @cmpny int while exists (select top 1 * from @company) begin set @cmpny = (select top 1 companyid from @company) insert into XYZ values('Annual', @cmpny) delete from @company where companyid = @cmpny end
An alternative of using a loop - Insert into Select Statement.
Test Setup SQL:
declare @ABC table( id int identity(1,1), companyid int ) insert into @ABC values(1); insert into @ABC values(3); insert into @ABC values(5); insert into @ABC values(5); insert into @ABC values(10); declare @XYZ table( period varchar(50), companyid int )
SQL to insert into @XYZ:
insert into @XYZ (period, companyid) select distinct 'Annual', companyid from @ABC;
What are in @ABC and @XYZ:
select * from @ABC; select * from @XYZ;
这篇关于如何在不使用游标的情况下为每个公司插入记的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文