Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2013-10-26
10
Medium Priority
?
337 Views
Last Modified: 2013-10-29
Hi all,

          I have a table of 500 rows with orderid column as primary key, and the values in are random say 10248,10250,10256,10258...... so on, now I have the code to copy the first 50 rows in a loop of 2, but the orderid column will be incremented by 1, the code is

create procedure USP_CopyingDuplicateRows @loop int, @top int as Begin Declare @i int = 1,@Maxid int = 0 while(@i <= @loop) Begin select @Maxid = max([OrderID]) from [T_MyOrders] insert into [T_MyOrders]([OrderID],[CustomerID] ,[EmployeeID],[OrderDate],[RequiredDate] ,[ShippedDate] ,[ShipVia],[Freight] ,[ShipName] ,[ShipAddress],[ShipCity] ,[ShipRegion] ,[ShipPostalCode],[ShipCountry] ) select top (@top) @Maxid+(row_number() over( order by [OrderID])) as [OrderID],[CustomerID] ,[EmployeeID],[OrderDate],[RequiredDate] ,[ShippedDate] ,[ShipVia],[Freight] ,[ShipName] ,[ShipAddress],[ShipCity] ,[ShipRegion] ,[ShipPostalCode],[ShipCountry] from T_myorders set @i=@i+1 END End

Open in new window


              But now I want the output as say if the table ends with 10800 as last orderid, if I copy first 10 rows, then it should give orderid with the gaps of orderid they are in, say first 10 orderid's are 10248,10250,10251,10256,10258.... now the output should be shown as 10801,10803,10804,10808,10810...... In this way the gaps i between orderid's should also be added and displayed in output. Can anyone help in this
0
Comment
Question by:Aparanjith
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39603347
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"?
0
 

Author Comment

by:Aparanjith
ID: 39603350
Hey Paul,
                  Its the task I have been assigned, basically my lead is testing on me. And I am using sql 2008
0
 
LVL 41

Expert Comment

by:Sharath
ID: 39604821
I did not understand your question well. Can you explain with some sample records and what exactly you are looking for?
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:Aparanjith
ID: 39605150
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.
0
 
LVL 32

Expert Comment

by:awking00
ID: 39606346
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.
0
 

Author Comment

by:Aparanjith
ID: 39606547
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
0
 
LVL 41

Expert Comment

by:Sharath
ID: 39606766
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

Open in new window

0
 

Author Comment

by:Aparanjith
ID: 39607034
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
0
 
LVL 41

Accepted Solution

by:
Sharath earned 2000 total points
ID: 39607815
I understand what you are looking for. Can you try this query.
declare @MaxOrderID int,@loop int
select @MaxOrderID = max(OrderID) from T_myorders
select @loop = 5

;with cte as (
  select top (@loop) *,row_number() over (order by OrderID) rn 
    from T_myorders
   order by OrderID),
cte2 as (
select c1.*,isnull(c1.OrderID-c2.OrderID,1) Diff
  from cte c1
  left join cte c2 on c1.rn = c2.rn + 1)
insert into T_myorders
	  ([OrderID], 
	   [CustomerID], 
	   [EmployeeID], 
	   [OrderDate], 
	   [RequiredDate], 
	   [ShippedDate], 
	   [ShipVia], 
	   [Freight], 
	   [ShipName], 
	   [ShipAddress], 
	   [ShipCity], 
	   [ShipRegion], 
	   [ShipPostalCode], 
	   [ShipCountry]) 
select @MaxOrderID+(select sum(Diff) from cte2 c2 where c2.OrderID <= c1.OrderID) OrderID,
         [CustomerID], 
		 [EmployeeID], 
		 [OrderDate], 
		 [RequiredDate], 
		 [ShippedDate], 
		 [ShipVia], 
		 [Freight], 
		 [ShipName], 
		 [ShipAddress], 
		 [ShipCity], 
		 [ShipRegion], 
		 [ShipPostalCode], 
		 [ShipCountry]
  from cte2 c1;

select * from T_myorders order by OrderID 

Open in new window


I have tested here with some sample data and it seems to be working fine.
http://sqlfiddle.com/#!3/f8779/30
0
 

Author Closing Comment

by:Aparanjith
ID: 39607954
Thank you Sharath
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question