How to insert records per company without using cursor

Question

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 28-Sep-14 21:12pm

Torakami

Updated 28-Sep-14 23:58pm

Herman<T>.Instance

v3

Add a Solution

3 solutions

Solution 2

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

Posted 28-Sep-14 22:05pm

Thomas Nielsen - getCore

Solution 3

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;

Posted 28-Sep-14 22:48pm

jaket-cp

Updated 28-Sep-14 23:40pm

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 ..

jaket-cp 3-Oct-14 12:30pm

Did you need more clarification on insert into select statement?

Add a Solution

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

Adnan Malikk · Accepted Answer · 2014-09-28T21:45:00

Solution 1

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

Posted 28-Sep-14 21:45pm

Adnan Malikk

Comments

Torakami 29-Sep-14 4:39am

Thanks ... this is what i was looking for ...

How to insert records per company without using cursor

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

How to insert records per company without using cursor

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us