Insert duplicate records into an alternative table

  • Insert duplicate records into an alternative table

    I need an insert script that selects records from TempCustomer table, inserts non- duplicate cellphoneNo into Contact table, and the duplicate ones into ContactAudit. And Finally delete the records inserted into either Contact and/or ContactAudit tables from TempCustomer table.

    Thank you in advance.

    CREATE TABLE [dbo].[TempCustomer](

    [CellphoneNo] [char](15) NULL,

    [CreatedOnDt] [datetime] NULL,

    [FileName] [char](20) NULL,

    [CustomerID] [int] IDENTITY(1,1) NOT NULL,

    CONSTRAINT [PK_TempCustomer] PRIMARY KEY CLUSTERED

    (

    [CustomerID] ASC

    ) ON [PRIMARY]

    CREATE TABLE [dbo].[Contact](

    [ContactID] [int] IDENTITY(1,1) NOT NULL,

    [CellphoneNo] [char](15) NOT NULL,

    [StatusInd] [char](1) NULL,

    [FileName] [char](20) NULL,

    [CreatedOnDt] [datetime] NULL,

    CONSTRAINT [PK_ContactID] PRIMARY KEY CLUSTERED

    (

    [ContactID] ASC

    ) ON [PRIMARY],

    CREATE TABLE [dbo].[ContactAudit](

    [ContactAuditID] [int] NOT NULL,

    [CellphoneNo] [char](15) NULL,

    [FileName] [char](20) NULL,

    [CreatedOnDt] [datetime] NULL,

    CONSTRAINT [PK_ContactAuditID] PRIMARY KEY CLUSTERED

    (

    [ContactAuditID] ASC

    ) ON [PRIMARY]

  • Please will you provide some sample data in the form of INSERT statements, and show us what you've already tried? You'll find that more people will be willing to help you if it looks as if you made some effort to solve this yourself.

    Thanks

    John

  • Hi,

    Sorry about that.

    Here is my sample script:

    ALTER PROC spAVM_TempCustomer_I

    AS

    BEGIN

    DECLARE @allcount INT,

    @CellphoneNo VARCHAR(15),

    @Filename VARCHAR(50),

    @Count INT,

    @ContactID INT

    CREATE TABLE #TempCustomer (CellphoneNo varchar(15))

    INSERT #TempCustomer

    SELECT CellphoneNo FROM TempCustomer

    SELECT @allcount = COUNT(*) FROM #TempCustomer

    WHILE @allcount > 0

    SELECT @CellphoneNo = CellphoneNo

    FROM #TempCustomer

    SELECT @Count = count(CellphoneNo)

    FROM #TempCustomer

    WHERE CellphoneNo = @CellphoneNo

    IF @Count > 0

    INSERT INTO Contact

    (CellphoneNo, StatusInd, FileName, CreatedOnDt)

    VALUES(@CellphoneNo, '', @FileName, getdate())

    DELETE #TempCustomer WHERE CellphoneNo = @CellphoneNo

    ELSE

    INSERT INTO ContactAudit

    (ContactAuditID, CellphoneNo, FileName ,CreatedOnDt)

    VALUES('', @CellphoneNo, @FileName, getdate())

    DELETE #TempCustomer WHERE CellphoneNo = @CellphoneNo

    SELECT @allcount = COUNT(*) FROM #TempCustomer

    END--WHILE

    DROP TABLE #TempCustomer

  • DROP TABLE

    dbo.TempCustomer,

    dbo.Contact,

    dbo.ContactAudit;

    GO

    CREATE TABLE [dbo].[TempCustomer](

    [CellphoneNo] [char](15) NULL,

    [CreatedOnDt] [datetime] NULL,

    [FileName] [char](20) NULL,

    [CustomerID] [int] IDENTITY(1,1) NOT NULL,

    CONSTRAINT [PK_TempCustomer] PRIMARY KEY CLUSTERED

    (

    [CustomerID] ASC

    )) ON [PRIMARY]

    GO

    CREATE TABLE [dbo].[Contact](

    [ContactID] [int] IDENTITY(1,1) NOT NULL,

    [CellphoneNo] [char](15) NOT NULL,

    [StatusInd] [char](1) NULL,

    [FileName] [char](20) NULL,

    [CreatedOnDt] [datetime] NULL,

    CONSTRAINT [PK_ContactID] PRIMARY KEY CLUSTERED

    (

    [ContactID] ASC

    )) ON [PRIMARY]

    GO

    CREATE TABLE [dbo].[ContactAudit](

    [ContactAuditID] [int] IDENTITY (1,1) NOT NULL,

    [CellphoneNo] [char](15) NULL,

    [FileName] [char](20) NULL,

    [CreatedOnDt] [datetime] NULL,

    CONSTRAINT [PK_ContactAuditID] PRIMARY KEY CLUSTERED

    (

    [ContactAuditID] ASC

    )) ON [PRIMARY]

    GO

    INSERT dbo.TempCustomer

    (CellphoneNo, CreatedOnDt, [FileName])

    VALUES ('012345678901000', CURRENT_TIMESTAMP, '20 char file name'),

    ('012345678901001', CURRENT_TIMESTAMP, '20 char file name'),

    ('012345678901001', CURRENT_TIMESTAMP, '20 char file name'),

    ('012345678901002', CURRENT_TIMESTAMP, '20 char file name'),

    ('012345678901003', CURRENT_TIMESTAMP, '20 char file name'),

    ('012345678901004', CURRENT_TIMESTAMP, '20 char file name'),

    ('012345678901004', CURRENT_TIMESTAMP, '20 char file name'),

    ('012345678901004', CURRENT_TIMESTAMP, '20 char file name'),

    ('012345678901005', CURRENT_TIMESTAMP, '20 char file name'),

    ('012345678901006', CURRENT_TIMESTAMP, '20 char file name');

    INSERT dbo.Contact

    (CellphoneNo, StatusInd, [FileName], CreatedOnDt)

    SELECT D.CellphoneNo,

    StatusInd = 'X',

    D.[FileName],

    D.CreatedOnDt

    FROM (

    DELETE Input

    OUTPUT deleted.CellphoneNo,

    deleted.[FileName],

    deleted.CreatedOnDt

    FROM (

    SELECT *,

    group_count = COUNT(*) OVER (PARTITION BY CellphoneNo)

    FROM dbo.TempCustomer

    ) Input

    WHERE group_count = 1

    ) D;

    GO

    INSERT dbo.ContactAudit

    (CellphoneNo, [FileName], CreatedOnDt)

    SELECT D.CellphoneNo,

    D.[FileName],

    D.CreatedOnDt

    FROM (

    DELETE Input

    OUTPUT deleted.CellphoneNo,

    deleted.[FileName],

    deleted.CreatedOnDt

    FROM (

    SELECT *,

    group_count = COUNT(*) OVER (PARTITION BY CellphoneNo)

    FROM dbo.TempCustomer

    ) Input

    WHERE group_count > 1

    ) D;

    GO

    SELECT *

    FROM dbo.Contact;

    GO

    SELECT *

    FROM dbo.ContactAudit;

    GO

    Paul

  • Thank you very much. The script works as expected.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply