CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Mr. Blue
Mr. Blue

Reputation: 113

Insert into SQL table

I'm trying to insert invoice numbers from one database to another. The new database, invoiceNo is the primary key. There are duplicate invoice numbers from the original database. I found the duplicates using 

SELECT  SERIAL_NO,
COUNT(*) as cnt    
FROM HISTORY_INCIDENTS
group by SERIAL_NO having COUNT(*) > 1
order by COUNT(*) desc

I would like to place a -1 at the end of the second invoice so they will be unique. Not sure how to do this is SQL

Upvotes: 1

Views: 118

Answers (2)

laylarenee
laylarenee

Reputation: 3284

I think this will work.

I used the ROW_NUMBER() function to calculate the number of times a serial number occurred in a inner derived table. In the main query, I make decisions on how to craft the new serial number based on the number of occurrences I found.

PS: done without an SQL editor.

SELECT
     SERIAL_NO
    ,SERIAL_NO + CASE WHEN [SERIAL_NO_Occurrence] > 1 THEN '-' + CAST([SERIAL_NO_Occurrence]-1 AS VARCHAR) ELSE '' END AS [SERIAL_NO_New]
FROM (

    -- inner derived table to select number of occurrences of each serial number
    SELECT      SERIAL_NO
                ,ROW_NUMBER() OVER (
                    PARTITION BY SERIAL_NO
                    ORDER BY SERIAL_NO -- you should also have a PK field here to have deterministic results I think
                 ) AS [SERIAL_NO_Occurrence]
    FROM        HISTORY_INCIDENTS
) AS T1;

Upvotes: 0

Christian Phillips
Christian Phillips

Reputation: 18749

You could use a CTE like this...

;
WITH    cteDups
          AS ( SELECT   SERIAL_NO ,
                        RN = ROW_NUMBER() OVER 
              ( PARTITION BY SERIAL_NO ORDER BY SERIAL_NO )
               FROM     HISTORY_INCIDENTS
             )
    SELECT  CASE RN
              WHEN 2 THEN SERIAL_NO + '-1'
              ELSE SERIAL_NO
            END AS SERIAL_NO
    FROM    cteDups

see the fiddle. This will create a row number of 2 when there's a duplicate, you can then use a CASE to check for any 2's, and assign the -1 to that record.

EDIT: based on comment

DECLARE @HISTORY_INCIDENTS TABLE ( SERIAL_NO INT );
DECLARE @NewHISTORY_INCIDENTS TABLE ( SERIAL_NO VARCHAR(10) );

INSERT  INTO @HISTORY_INCIDENTS
        ( SERIAL_NO )
VALUES  ( 1 );
INSERT  INTO @HISTORY_INCIDENTS
        ( SERIAL_NO )
VALUES  ( 2 );
INSERT  INTO @HISTORY_INCIDENTS
        ( SERIAL_NO )
VALUES  ( 2 );
INSERT  INTO @HISTORY_INCIDENTS
        ( SERIAL_NO )
VALUES  ( 2 );

;
WITH    cteDups
          AS ( SELECT   SERIAL_NO ,
                        RN = ROW_NUMBER() OVER ( PARTITION BY SERIAL_NO 
                                                           ORDER BY SERIAL_NO )
               FROM     @HISTORY_INCIDENTS
             )
    INSERT  INTO @NewHISTORY_INCIDENTS
            ( SERIAL_NO
            )
            SELECT  CASE RN
                      WHEN 2 THEN CAST(SERIAL_NO AS VARCHAR(10)) + '-1'
                      ELSE CAST(SERIAL_NO AS VARCHAR(10))
                    END AS SERIAL_NO
            FROM    cteDups

SELECT   SERIAL_NO
FROM    @NewHISTORY_INCIDENTS

Upvotes: 2

Related Questions