how to do it without using cursor?

Hot Topic

Performance - Slow load times for SSIS Packages
Custom Data Flow Transformation loosing CustomProperties
Reporting service with sql server 2005
URGENT: Insert and Update in a transaction is getting blocked
SQLServer2K5_BOL.msi install fails with "not supported on the operating system".
SqlServer2000 can support merge replication with SqlCe3.0??
Date field
Embed a Data Mining model in ASP.NET
Measures that depends on other measures
Limitation for UDAs

SQL Server

Using SSMS to Manage SSIS
SSRS 2005 Export to PDF Missing Data
cannot convert between unicode and non-unicode
Good SSIS Example tutorial - User Variables
T-SQL Catch-all error handling?
Distributed Transaction completed error
Calling user defined function from other server
Copy Reporting services Reports from Beta to production
Incorrect Results with t-sql Query in SQL Server 2005
Replication Deadlocks

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

Answer this question

how to do it without using cursor?

  • Karen Grube

    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.


  • KannanPV

    Hi,

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

    http://www.sql-server-performance.com/dp_no_cursors.asp
  • how to do it without using cursor?
Answer this question