I am trying to Insert selected items, I am confused if I need to use cursor or the simple insert combined with select is enough?

Question

I have came up with this query, does not produce any error while praise, but I Don't know if I can use this select statement as the value

insert into store(c1 ,artno, c3, c4, c5, c6, c7 )
           select '1', a.artno, 0,0.0,null,null,null 
           from art a
              left join store s on s.artno=a.artno
              inner join status b on a.artno = b.artno
    where b.state ='5'  
      and s.artno is null  
      and a.artgroup not in('63','280')

I also saw another alternative which may be used but not sure if it can be used to insert as my requirement, I saw this implemented in stored procedure so just grabbed the idea if it can be used?

declare @artno as varchar(150); 
declare @count as tinyint

Declare cS CURSOR For
               select  a.artno
               from art a
              left join store s on s.artno=a.artno
              inner join status b on a.artno = b.artno
    where b.staus ='5'  
      and s.artno is null  
      and a.artgroup not in('63','280') 


Open cS
Fetch NEXT from cS into @artno

While @@FETCH_STATUS=0

select @count=COUNT(*) from store where artno=@artno
if @count=0 

 BEGIN

insert into store(c1 ,artno, c3, c4, c5, c6, c7 )
           values('1', a.artno, 0,0.0,null,null,null)                 

    fetch next from cS into @artno                 
 END 
 close cS
 deallocate cS

Some explanation which to use and which not to and why, would help me for my knowledge as well.

[Why is it considered bad practice to use cursors in SQL Server?](http://stackoverflow.com/questions/58141/why-is-it-considered-bad-practice-to-use-cursors-in-sql-server) — Chris Gessler, Aug 09 '12 at 11:57
When choosing between a cursor based or set based solution, you should always use the set based solution. 1. I have yet to see a cursor based solution outperform a set based one. 2. The cursor based solution is prone to concurrency problems. 3. Readability of this set based solution is much better. — Lieven Keersmaekers, Aug 09 '12 at 11:58
@Lieven Thanks for the edit and your suggestion, I am new to DB , this really helps +1. — tough, Aug 09 '12 at 13:04

score 1 · Accepted Answer · answered Aug 09 '12 at 12:00

1

Cursors are memory intensive and time consuming. Whereas SELECT INTO would have pre-constructed the table content to be inserted and can do it in a single stretch. Also in case of SELECT INTO, SQL Engine has chance of optimizing the data fetch whereas in case of cursor, you are forcing the DB to fetch rows sequentially and no optimization is possible.

http://blog.sqlauthority.com/2011/08/10/sql-server-use-insert-into-select-instead-of-cursor/

Go through the above link which advocates my statement.

answered Aug 09 '12 at 12:00

sundar

1,760
12
28

Thanks for your answer, and the link for clarification. As the above code suggests and my little explanation in the question, I am trying to use the result set in row wise to insert into other table `store` .Will my first option do row-wise operation so that the `artno` I am getting form select are individually inserted into the store table with the `where` filter working properly? – tough Aug 09 '12 at 13:21

score 1 · Answer 2 · answered Aug 09 '12 at 14:18

Since it hasn't been mentioned anywhere else, there is another problem with cursors. Every insert/update to the database requires round trip communication from the client to the server.

This is actually worse than it sounds from a performance perspective. A database is often designed to efficiently batch insert data into tables. The effort to insert 100 rows is rarely 100 times the effort to insert 1 row. More typically, it would be just just a bit longer.

When you use cursors, you are forcing "serialization" of the operations. So, the entire operation for the first row has to complete, before you can move to the second one. This can radically decrease performance.

As other have mentioned, always use set based operations when you can. If you can't, think about changing the problem so you can ;-)

Thanks, I will surely follow this, I tried to follow the help form the Microsoft website and dynamic help topic in the Server Management Studio 2008 but they lack enough information, and are not to the point for newbies. +1 — tough, Aug 09 '12 at 16:07

I am trying to Insert selected items, I am confused if I need to use cursor or the simple insert combined with select is enough?

2 Answers2