copy row with all children

thanks,
this is exactly what is want,
but  
    -did not copy the @FromProductID, its copy the children only
    -I have some cols to  copy (say x, y, z)

I modify the SP to be

ALTER PROCEDURE [dbo].[CopyChildProducts] 
	-- Add the parameters for the stored procedure here
	  @FromProductID INT,   @ToProductID INT
AS
BEGIN


DECLARE @CurrProductID INT,   @NewProductID INT;

DECLARE crsProduct CURSOR FOR
	SELECT ProductID
	FROM Products
	WHERE ParentProductID = @FromProductID;

OPEN crsProduct;
FETCH NEXT FROM crsProduct INTO @CurrProductID;

WHILE @@FETCH_STATUS = 0
BEGIN
   INSERT INTO Products (ParentProductID ,[ProductNameEN)   
   
      select @ToProductID, ProductNameEN]
           From Products where ProductID= @FromProductID 

   SET @NewProductID = SCOPE_IDENTITY()

   EXECUTE CopyChildProducts  @CurrProductID,  @NewProductID

   FETCH NEXT FROM crsProduct INTO @CurrProductID;
END

CLOSE crsProduct
DEALLOCATE crsProduct 
END

Select allOpen in new window

but I have some problems , it copy first level only and put them inside each other

What is your SQL Server version?

2008

I believe there is a syntax issue in the INSERT clause, should be...

      INSERT INTO Products (ParentProductID ,ProductNameEN)  
      SELECT @ToProductID, ProductNameEN
      FROM Products where ProductID= @CurrProductID

I change it but I got wrong result I got :

where I should got

Can you post some sample data from your table with expected result?

I have those product structure:

I just need to make a copy of the product 229 and its children structured products

did u read my question?

You want an SP? If the code posted works for you, It can be easily converted into an SP. Let me know.

the code posted not works as required ..

Provide some sample data and expected result

ohhh GOD, please I don't have time for you , if you cant understand the question  please ignore it..

alright. I am here to help and gain knowledge by helping. If my posts won't help you, you can obviously look for more experts comments.
 
The more information, detailed explanation you can provide, the faster you can get an answer.

When you said the code posted not works as required, you need to explain what is required and where the current script is lagging. If you cannot explain, its tough to answer and I can ignore your question right here.

Finally, I am here as a volunteer and don't get dollars for answering the questions. Time is important for everyone and I answer the questions in my leisure time only.

Thanks,