Click here to Skip to main content
15,884,986 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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 ..


SQL
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 ...
Posted
Updated 28-Sep-14 23:58pm
v3

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
 
Share this answer
 
Comments
Torakami 29-Sep-14 4:39am    
Thanks ... this is what i was looking for ...
SQL
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
 
Share this answer
 
An alternative of using a loop - Insert into Select Statement.

Test Setup SQL:
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:
SQL
insert into @XYZ
(period, companyid)
select distinct 
'Annual', companyid
from @ABC;


What are in @ABC and @XYZ:
SQL
select * from @ABC;
select * from @XYZ;
 
Share this answer
 
v2
Comments
Torakami 29-Sep-14 5:14am    
whats this ... ??/
jaket-cp 29-Sep-14 5:18am    
A select statement can be used to insert into a table.
Check out.
http://technet.microsoft.com/en-us/library/dd776381(v=sql.105).aspx
A. Using the SELECT and EXECUTE options to insert data from other tables
Jörgen Andersson 29-Sep-14 5:49am    
This is the correct way to handle your problem.
No cursors, no temptables. A direct insert from the query, you don't get better performance than that.
Torakami 29-Sep-14 7:06am    
ok thanks ,
Torakami 29-Sep-14 7:07am    
Abc contains companyId // whic i am taking as i want to insert those id into my xyz table ..

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