how to do it without using cursor?

Hi,

I need to insert several rows into two tables like: table product and table productDetail.

The product table has an identity column and the productDetail table has an id column references to the identity column.

So when one row is inserted into the product table, the corresponding productDetail row should be populated.

If there is only one row to insert, it won't be a problem. How can I insert several rows? I know I can use cursor, but I always heard about the performance issues related. Is there a chance to avoid cursor?

Many Thanks

Hi,

Try out the below mentioned link ,might be it helps you out.

http://www.sql-server-performance.com/dp_no_cursors.asp

|||

Please don't use cursors or WHILE loop for these sort of operations. It will not scale well and perform poorly. In SQL Server 2005, you can do the following:

-- assuming that you are have the data to be inserted

-- in a temporary table for example:

begin tran

declare @.newprods table ( productid int, productname varchar(50) )

insert into product (productname...)

ouput inserted.productid, inserted.productname into @.newprods

select distinct productname ....

from #products

insert into productdetail (productid, sellername...)

select np.productid, p.sellername

from #products as p

join @.newprods as np

on np.productname= p.productname

...

commit

You can use similar logic in SQL Server 2000 also.