CODEWITHSUNDEEP
sqlsql-servert-sql
Internet Engineer
Internet Engineer

Reputation: 2534

Partially Duplicate Data in the same SQL Table

I have a table with 20,000 entries.

I need to duplicate "some" of the data, but with a new company Name.

The table has a unique ID that is not auto incremental, therefore during each insert I need to find the MAX(UniqueID) and add 1.

The following scripts works, but has terrible performance.

DECLARE @RowCount AS INTEGER;
SELECT  @RowCount = COUNT(1)
FROM    [dbo].[TableAAA];


DECLARE @intFlag INT;
SET @intFlag = 1;
WHILE ( @intFlag <= @RowCount )
    BEGIN
        INSERT  INTO [dbo].[TableAAA]
                ( UniqueID ,
                  company ,
                  Agent ,
                  Phone
                )
                SELECT TOP 1
                        ( SELECT    MAX(UniqueID) + 1
                          FROM      [dbo].[TableAAA]
                        ) ,
                        'New Company' ,
                        Agent ,
                        Phone
                FROM    [dbo].[TableAAA] c
                WHERE   c.companyid = 'Old Company'
                        AND c.phone NOT IN ( SELECT Phone
                                             FROM   [dbo].[TableAAA]
                                             WHERE  company = 'New Company' );


        SET @intFlag = @intFlag + 1;
    END;

Upvotes: 1

Views: 50

Answers (1)

S3S
S3S

Reputation: 25112

I would use the MAX(UniqueID) as a SEED and then increment off that in a set based approach

declare @Seed int = (select MAX(UniqueID) FROM [dbo].[TableAAA])

SELECT 
   ID = row_number() over (order by (select null)) + @Seed
   'New Company',
    Agent,
    Phone
INTO 
    #Staging
FROM
    [dbo].[TableAAA] c
WHERE   
    c.companyid = 'Old Company'
    AND c.phone NOT IN ( SELECT Phone
                         FROM   [dbo].[TableAAA]
                         WHERE  company = 'New Company' )

INSERT INTO  [dbo].[TableAAA]
              (UniqueID,
               company,
               Agent,
               Phone)
SELECT
    ID,
    [New Company],
    Agent,
    Phone
FROM #Staging

DROP TABLE #Staging

Upvotes: 1

Related Questions