I need to copy the rows from table where it has id column as primary key and id values has random values

are you using SQL 2012? (this provides the lead/lag functions)

but why are you renumbering orderid anyway? can't you just use it "as is"?

Hey Paul,
                  Its the task I have been assigned, basically my lead is testing on me. And I am using sql 2008

I did not understand your question well. Can you explain with some sample records and what exactly you are looking for?

Hey sharath,
                         This is what I am looking for, lets say I have 50 records in a table with different rows and orderid column as primary key, the code which I have provided allows me to copy top rows say top 30 rows by incrementing the orderid column sequentially, like 51,52,53,54,55....up to 80 as we are copying 30 rows.

But say if we have gaps in orderid columns in my table of 50 rows, then my output of orderid when I copy the rows should also increment according the gaps,
as say I have top 10 rows as 1, 2,4,6,9,11,12.......so on, now as there is differece of 1 between 2 and 4 and similarly there is difference of 3 between 6 and 9, so now the output of orderid's which will come should also come according to these gaps, like say we are copying top5 rows then the orderid shoulld be 51,52,54,56,59 as there are gaps in orderid's the output of copying them should also be done considering these gaps.

Your explanation is still rather vague to me. Can you provide some sample data and your expected output along with the criteria used for that output? I sense you're trying to use the row_number() function but that will never create gaps. Perhaps there is another means of achieving your desired results.

This is sample date I get when I query for select * from T_myorders
OrderID      CustomerID      EmployeeID      OrderDate      RequiredDate      ShippedDate      ShipVia      Freight
10249      TOMSP      6      00:00.0      00:00.0      00:00.0      1      11.61      
10251      VICTE      3      00:00.0      00:00.0      00:00.0      1      41.34      
10253      HANAR      3      00:00.0      00:00.0      00:00.0      2      58.17      
10254      CHOPS      5      00:00.0      00:00.0      00:00.0      2      22.98
10256      WELLI      3      00:00.0      00:00.0      00:00.0      2      13.97      
10257      HILAA      4      00:00.0      00:00.0      00:00.0      3      81.91
10259      CENTC      4      00:00.0      00:00.0      00:00.0      3      3.25      
10260      OTTIK      4      00:00.0      00:00.0      00:00.0      1      55.09      
10261      QUEDE      4      00:00.0      00:00.0      00:00.0      2      3.05      
10262      RATTC      8      00:00.0      00:00.0      00:00.0      3      48.29      
10264      FOLKO      6      00:00.0      00:00.0      00:00.0      3      3.67      


Now when I copy the first 5  records I need the output with the difference of numbers in orderid accordingly to the table values say, for first five records it should give the output as
OrderID      CustomerID      EmployeeID      OrderDate      RequiredDate      ShippedDate      ShipVia      Freight
10265      TOMSP      6      00:00.0      00:00.0      00:00.0      1      11.61
10267      VICTE      3      00:00.0      00:00.0      00:00.0      1      41.34
10269      HANAR      3      00:00.0      00:00.0      00:00.0      2      58.17
10270      CHOPS      5      00:00.0      00:00.0      00:00.0      2      22.98
10272      WELLI      3      00:00.0      00:00.0      00:00.0      2      13.97

In this way I need the output

I understand to some extent. I dont suggest a WHILE loop for this. You can try like this.

CREATE PROCEDURE USP_CopyingDuplicateRows @loop INT, 
                                          @top  INT 
AS 
  BEGIN 
      DECLARE     @i INT = 1, 
              @Maxid INT = 0 

      INSERT INTO [T_MyOrders] 
                  ([OrderID], 
                   [CustomerID], 
                   [EmployeeID], 
                   [OrderDate], 
                   [RequiredDate], 
                   [ShippedDate], 
                   [ShipVia], 
                   [Freight], 
                   [ShipName], 
                   [ShipAddress], 
                   [ShipCity], 
                   [ShipRegion], 
                   [ShipPostalCode], 
                   [ShipCountry]) 
      SELECT OrderID, 
             [CustomerID], 
             [EmployeeID], 
             [OrderDate], 
             [RequiredDate], 
             [ShippedDate], 
             [ShipVia], 
             [Freight], 
             [ShipName], 
             [ShipAddress], 
             [ShipCity], 
             [ShipRegion], 
             [ShipPostalCode], 
             [ShipCountry] 
        FROM (SELECT ROW_NUMBER() 
                       OVER( 
                         ORDER BY [OrderID]) AS rn, 
                     * 
                FROM T_myorders) t1 
       WHERE rn <= @top 
  END

Select allOpen in new window

Hey sharath,
As I mentioned in my question, the orderid column is primary key, so it will not accept the duplicate values. Please can you check that. As we I query that I am getting the primary key violation error.

Thanks,
Aparanjit

Thank you Sharath